Oracle Tips #3 [Extract Metadata Info ]
by using some simple view in Oracle you can extract a lot of information about your DB schema, some explicit samples :
(assuming that MyDBOwner is the owner of the DB and ACTIVITY is a table of a schema, this sample is succesfully tested on Oracle 10g)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
-- LIST OF USERS select OWNER,COUNT(DISTINCT TABLE_NAME) from ALL_TAB_COLUMNS GROUP BY OWNER -- LIST OF TABLES PER OWNER select table_name from ALL_TAB_COLUMNS where owner = 'MyDBOwner' group by table_name -- LIST OF FIELDS PER TABLE select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE, CHAR_LENGTH from ALL_TAB_COLUMNS where owner = 'MyDBOwner' AND TABLE_NAME = 'ACTIVITY' -- FINDING PRIMARY KEYS select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE, CHAR_LENGTH from ALL_TAB_COLUMNS where owner = 'MyDBOwner' AND TABLE_NAME = 'ACTIVITY' -- A single character that indicates the type of constraint: 'C' = NOT NULL, 'P' = PRIMARY KEY, 'U' = UNIQUE, and 'R' = FOREIGN KEY. SELECT UC.CONSTRAINT_NAME, UCC.* FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UCC WHERE UC.OWNER='MyDBOwner' AND UC.TABLE_NAME = 'ACTIVITY' AND UC.CONSTRAINT_TYPE = 'P' AND UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME |
a great article about this is : http://college.yukondude.com/2002_09_comp210/html/note-container.php?file=10^Handout^Oracle_Metadata and on wiki too at this address http://en.wikipedia.org/wiki/Oracle_metadata#Example_2:_finding_columns
bye ivan
