Queries to retrieve table metadata

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