Oracle metadata
From Wikipedia, the free encyclopedia
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:
- ALL_TABLES - list of all tables in the current database
- ALL_TAB_COLS - list of all columns in the database
The views have prefixes "USER_", "ALL_" and "DBA_".
Contents |
[edit] 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;
[edit] 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%';
[edit] 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;
[edit] 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: __
[edit] 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.
