Queries to retrieve table metadata

This script will give you a quick view of Oracle tables, including their relationships, indexes and grants.

This utility provides a quick view of tables, including their relationships (parent and children tables/columns), indexes and grants. It was originally written for version 7, but the script below works on version 8.

 clear col clear breaks set echo off set verify off set feedback off set linesize 100 set pagesize 600 col cx noprint format a1 col c1 new_value xtable format a1 noprint col c2 format a30 heading 'column name' col c3 format a15 heading 'data type' col c4 format a3 heading 'nl?' col c5 format 99999 heading 'col #' col c6 format 99 heading 'seq' col c7 format a11 heading 'keys ' col c8 new_value xdate noprint format a1 col c9 format a6 heading 'unique' col cz heading 'index name' col ca heading 'grantor' form a15 col cb heading 'grantee' form a15 col cc heading 'column' form a20 col cd heading 'privilege' form a15 col ce heading 'admin?' form a10 break on c7 accept owner prompt 'owner: ' accept table prompt 'table: ' set heading off set termout off spool mytable select 'table: '||owner||'.'||table_name from all_tables where owner = upper('&owner') and table_name = upper('&table'); select 'date: '||to_char(sysdate,'mm/dd/yy') from dual; set heading on -- main listing: columns alphabetically select t3.name c2, decode(t3.type#, 1,'varchar2('||t3.length||')', 2,decode(t3.precision#, null,'number()', 'number('||t3.precision#||'.' ||t3.scale||')'), 8,'long', 12,'date', 23,'raw', 24,'long raw', 69,'rowid', 96,'char('||t3.length||')', 106,'mlslabel', '???') c3, decode(t3.null$,1,'not',' ') c4, to_char(sysdate,'mm/dd/yy') c8 from sys.user$ t1, sys.obj$ t2, sys.col$ t3 where t1.name = upper('&owner') and t1.user# = t2.owner# and t2.name = upper('&table') and t2.status != 0 and t2.obj# = t3.obj# order by c2; -- index listing select t6.name cz, t4.pos# c6, decode(bitand(t5.property,1),1,'YES',' ') c9, t3.name c2 from sys.user$ t1, sys.obj$ t2, sys.col$ t3, sys.icol$ t4, sys.ind$ t5, sys.obj$ t6 where t1.name = upper('&owner') and t1.user# = t2.owner# and t2.name = upper('&table') and t2.status != 0 and t2.obj# = t3.obj# and t3.obj# = t4.bo# and t3.col# = t4.col# and t4.obj# = t5.obj# and t4.obj# = t6.obj# order by cz, c6; -- parent table.column listing clear col col c1 heading 'parent column' col c2 heading 'child table.column' select t6.name c1, rtrim(t5.name)||'.'||rtrim(t4.name) c2 from sys.cdef$ t1, sys.ccol$ t2, sys.ccol$ t3, sys.col$ t4, sys.obj$ t5, sys.col$ t6 where t1.type# = 4 and t1.robj# = (select obj# from sys.obj$ where owner# = (select user# from sys.user$ where name = upper(' &owner')) and name = upper('&table')) -- find child table.column and t1.obj# = t3.obj# and t1.con# = t3.con# and t3.obj# = t5.obj# and t3.obj# = t4.obj# and t3.col# = t4.col# -- find parent column and t1.robj# = t2.obj# and t1.rcon# = t2.con# and t2.obj# = t6.obj# and t2.col# = t6.col# order by c1, c2; -- child table.column listing clear col clear break col c1 heading 'child column' col c2 heading 'parent table.column' select t4.name c1, rtrim(t5.name)||'.'||rtrim(t6.name) c2 from sys.cdef$ t1, sys.ccol$ t2, sys.ccol$ t3, sys.col$ t4, sys.obj$ t5, sys.col$ t6 where t1.type# = 4 and t1.obj# = (select obj# from sys.obj$ where owner# = (select user# from sys.user$ where name = upper(' &owner')) and name = upper('&table')) -- find child column and t1.obj# = t3.obj# and t1.con# = t3.con# and t3.obj# = t4.obj# and t3.col# = t4.col# -- find parent table.column and t1.robj# = t2.obj# and t1.rcon# = t2.con# and t2.obj# = t5.obj# and t2.obj# = t6.obj# and t2.col# = t6.col# order by c1, c2; -- privilege section clear col col ca heading 'grantor' form a15 col cb heading 'grantee' form a15 col cc heading 'column' form a20 col cd heading 'privilege' form a15 col ce heading 'admin?' form a10 select grantor ca, grantee cb, privilege cd, grantable ce from dba_tab_privs where owner = upper('&owner') and table_name = upper('&table') order by 1, 2, 3, 4; select grantor ca, grantee cb, column_name cc, privilege cd, grantable ce from dba_col_privs where owner = upper('&owner') and table_name = upper('&table') order by 1, 2, 3, 4; spool off set termout on set feedback on ttitle off btitle off prompt prompt Table listing is in MYTABLE.LST prompt


This was first published in January 2004

Dig deeper on Oracle MDM and metadata

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close