Oracle Database provides information about all of the tables, views, columns, and procedures in a database. This information about information is known as metadata. It is stored in two locations: data dictionary tables (accessed via built-in views) and a metadata registry. Other relational database management systems support an ANSI-standard equivalent called information schema. Views for metadata The total number of these views depends on the Oracle version, but is in a 1000 range. The main built-in views accessing Oracle RDBMS data dictionary tables are few, and are as follows: ALL_OBJECTS – list of all objects in the current database that are accessible to the current user; ALL_TABLES – list of all tables in the current database that are accessible to the current user; ALL_VIEWS – list of all views in the current database that are accessible to the current user; ALL_TAB_COLUMNS – list of all columns in the database that are accessible to the current user; ALL_ARGUMENTS – lists the arguments of functions and procedures that are accessible to the current user; ALL_ERRORS – lists descriptions of errors on all stored objects (views, procedures, functions, packages, and package bodies) that are accessible to the current user; ALL_OBJECT_SIZE – included for backward compatibility with Oracle version 5; ALL_PROCEDURES – (from Oracle 9 onwards) lists all functions and procedures (along with associated properties) that are accessible to the current user; ALL_SOURCE – describes the text (i.e. PL/SQL) source of the stored objects accessible to the current user; ALL_TRIGGERS – list all the triggers accessible to the current user. In addition there are equivalent views prefixed "USER_" which show only the objects owned by the current user (i.e. a more restricted view of metadata) and prefixed "DBA_" which show all objects in the database (i.e. an unrestricted global view of metadata for the database instance). Naturally the access to "DBA_" metadata views requires specific privileges. Example 1: finding tables Find all Tables that have PATTERN in the table name SELECT Owner AS Schema_Name, Table_Name FROM All_Tables WHERE Table_Name LIKE '%PATTERN%' ORDER BY Owner, Table_Name; Example 2: finding columns Find all tables that have at least one column that matches a specific PATTERN in the column name SELECT Owner AS Schema_Name, Table_Name, Column_Name FROM All_Tab_Columns WHERE Column_Name LIKE '%PATTERN%' ORDER BY 1,2,3; Example 3: counting rows of columns Estimate a total number of rows in all tables containing a column name that matches PATTERN