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)

-- 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