|
The Oracle application server and Oracle relational database keeps metadata in two areas: tables and a metadata registry. The Oracle RDBMS system keeps its data in several tables and creates several views: The views have prefixes "USER_", "ALL_" and "DBA_".
Example 1: finding tables Find all Tables that have PATTERN in the table name and are not backup or temporary tables SELECT TABLE_NAME FROM ALL_TABLES WHERE TABLE_NAME LIKE '%PATTERN%' ORDER BY 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 TABLE_NAME, COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE COLUMN_NAME LIKE '%PATTERN%'; Example 3: counting rows of columns Count number of total number of rows in all tables that with a column name that matches PATTERN == COLUMN DUMMY NOPRINT COMPUTE SUM OF NUM_ROWS ON DUMMY BREAK ON DUMMY SELECT NULL DUMMY, T.TABLE_NAME, C.COLUMN_NAME, T.NUM_ROWS FROM ALL_TABLES T, ALL_TAB_COLUMNS C WHERE T.TABLE_NAME = C.TABLE_NAME AND C.COLUMN_NAME LIKE '%PATTERN%' ORDER BY T.TABLE_NAME; Use of underscore in table and column names The underscore is a special SQL pattern match to a single character and should be escaped if you are in fact looking for an underscore character in the LIKE clause of a query. Just add the following after a LIKE statement: ESCAPE _ And then each literal underscore should be a double underscore: __ Oracle Metadata Registry The Oracle product Oracle Enterprise Metadata Manager (EMM) is an ISO/IEC 11179 compatible metadata registry. It stores administered metadata in a consistent format that can be used for metadata publishing. As of January 2006, EMM is available only through Oracle consulting services. See also | ||||||||
|
| |||||||||
 |
|
| |