/*
Created: Version 1: 01/30/2012 Jed S. Walker
Updated: Version 2: 03/20/2006 Jed S. Walker
Notes :
does not work on Oracle < 9i due to MView check
To do:
does not handle "" table names
Usage:
@jdesc user.table
@jdesc table
*/
set serveroutput on size 1000000
set verify off
set feedback off
set pages 50 linesize 120
set long 10000
col owner format a20
col object_name format a35
declare
n_exist number;
v_param1 varchar2(200);
v_seploc number;
v_schema_name user_tables.table_name%type;
v_table_name user_tables.table_name%type;
v_longmsg varchar2(4000);
v_type_atts varchar2(500);
cursor curs_syns (owner_val varchar2, tabname_val varchar2)
is select owner, synonym_name
from all_synonyms
where owner = owner_val
and table_name = tabname_val
order by owner, synonym_name;
cursor curs_tcols (owner_val varchar2, tabname_val varchar2)
is select column_name, data_type,
data_length, data_precision, data_scale,
decode(nullable,'Y','NULL','NOT NULL') nullable,
data_default
from all_tab_columns
where owner = owner_val
and table_name = tabname_val
order by column_id;
v_lenprec varchar2(20);
cursor curs_cons (owner_val varchar2, tabname_val varchar2)
is select constraint_name,
decode(constraint_type, 'P', 'Primary', 'U', 'Unique',
'C', 'Check', 'R', 'References', constraint_type)
constraint_type,
search_condition, r_constraint_name,
decode(delete_rule, 'NO ACTION', '', delete_rule) delete_rule,
decode(constraint_type, 'P', 1, 'U', 2, 'R', 3, 'C', 4, 5) sortorder
from all_constraints
where owner = owner_val
and table_name = tabname_val
order by sortorder, constraint_name;
v_fk_table_name user_tables.table_name%type;
v_temp varchar2(2000);
cursor curs_ccollist (owner_val varchar2, consname_val varchar2)
is select column_name
from all_cons_columns
where owner = owner_val
and constraint_name = consname_val
order by position;
v_criteria varchar2(2000);
cursor curs_inds (owner_val varchar2, tabname_val varchar2)
is select index_name, index_type, uniqueness
from all_indexes
where owner = owner_val
and table_name = tabname_val
order by uniqueness desc, index_name;
cursor curs_icollist (owner_val varchar2, indname_val varchar2)
is select column_name
from all_ind_columns
where index_owner = owner_val
and index_name = indname_val
order by column_position;
v_columns varchar2(2000);
cursor curs_trigs (owner_val varchar2, tabname_val varchar2)
is select trigger_name, trigger_type, triggering_event
from all_triggers
where owner = owner_val
and table_name = tabname_val
order by trigger_type, triggering_event, trigger_name;
begin
-- get parameters and determine if this is local schema or other schema
v_param1:='&1';
v_seploc:=instr(v_param1,'.');
if v_seploc = 0 then
v_schema_name:=upper(SYS_CONTEXT('USERENV','CURRENT_SCHEMA'));
v_table_name:=upper(v_param1);
else
v_schema_name:=upper(substr(v_param1,1,v_seploc-1));
v_table_name:=upper(substr(v_param1,v_seploc+1,length(v_param1)-v_seploc));
end if;
-- check for table existence or view
select count(1) into n_exist
from all_tables
where owner = v_schema_name
and table_name = v_table_name;
if n_exist = 0 then
select count(1) into n_exist
from all_views
where owner = v_schema_name
and view_name = v_table_name;
if n_exist = 0 then
dbms_output.put_line('Table ' || v_schema_name || '.' || v_table_name || ' does not exist');
else
dbms_output.put_line(chr(10) || v_schema_name || '.' || v_table_name || ' is a view:' || chr(10));
select text into v_longmsg from all_views where owner = v_schema_name and view_name = v_table_name;
dbms_output.put_line(v_longmsg);
end if;
else
-- show table name and type attributes
dbms_output.put_line(chr(10) || v_schema_name || '.' || v_table_name || chr(10));
-- show table type attributes
select decode(temporary,'Y','YES','N','NO') into v_temp
from all_tables
where owner = v_schema_name
and table_name = v_table_name;
dbms_output.put_line('Temporary Table => ' || v_temp);
select decode(iot_type,'IOT','YES','NO') into v_temp
from all_tables
where owner = v_schema_name
and table_name = v_table_name;
dbms_output.put_line('Index Organized Table (IOT) => ' || v_temp);
select partitioned into v_temp
from all_tables
where owner = v_schema_name
and table_name = v_table_name;
dbms_output.put_line('Partitioned => ' || v_temp);
select decode(cluster_name,null,'NO','YES') into v_temp
from all_tables
where owner = v_schema_name
and table_name = v_table_name;
dbms_output.put_line('Clustered Table => ' || v_temp);
select decode(compression,'ENABLED','YES','NO') into v_temp
from all_tables
where owner = v_schema_name
and table_name = v_table_name;
dbms_output.put_line('Compressed Table => ' || v_temp);
select decode(count(1),0,'NO','YES') into v_temp
from all_base_table_mviews
where owner = v_schema_name
and master = v_table_name;
dbms_output.put_line('Materialized Views => ' || v_temp);
select decode(count(1),0,'NO','YES') into v_temp
from all_policies
where object_owner = v_schema_name
and object_name = v_table_name;
dbms_output.put_line('Fine Grained Access Control => ' || v_temp);
-- show synonyms
dbms_output.put_line(chr(10));
n_exist:=0;
for rec_syn in curs_syns (v_schema_name,v_table_name) loop
dbms_output.put_line('Synonym: ' || rec_syn.owner || ' : ' || rec_syn.synonym_name);
n_exist:=n_exist+1;
end loop;
if n_exist = 0 then
dbms_output.put_line('There are no synonyms for this table.');
end if;
-- list columns, data type, null?, default
dbms_output.put_line(chr(10) || rpad('COLUMN',32) || chr(9) ||
rpad('TYPE',14) || chr(9) ||
rpad('NULLS',10) || chr(9) ||
'DEFAULT');
dbms_output.put_line(rpad('------',32) || chr(9) ||
rpad('----',14) || chr(9) ||
rpad('-----',10) || chr(9) ||
'-------');
for rec_col in curs_tcols (v_schema_name,v_table_name) loop
if rec_col.data_type in ('CHAR','VARCHAR2','NCHAR','NVARCHAR2') then
v_lenprec:='(' || rec_col.data_length || ')';
elsif rec_col.data_type in ('NUMBER') then
v_lenprec:='(' || rec_col.data_precision || ',' || rec_col.data_scale || ')';
else
v_lenprec:='';
end if;
dbms_output.put_line(rpad(rec_col.column_name,32) || chr(9) ||
rpad(rec_col.data_type || v_lenprec,14) || chr(9) ||
rpad(rec_col.nullable,10) || chr(9) ||
rec_col.data_default);
end loop;
-- constraints
dbms_output.put_line(chr(10) || rpad('CONSTRAINT',32) || chr(9) ||
rpad('TYPE',14) || chr(9) ||
'CRITERIA');
dbms_output.put_line(rpad('----------',32) || chr(9) ||
rpad('----',14) || chr(9) ||
'--------');
n_exist:=0;
for rec_con in curs_cons (v_schema_name,v_table_name) loop
if rec_con.constraint_type in ('Primary','Unique') then
v_criteria:='(';
for rec_collist in curs_ccollist(v_schema_name,rec_con.constraint_name) loop
if v_criteria != '(' then
v_criteria:=v_criteria||',';
end if;
v_criteria:=v_criteria || rec_collist.column_name;
end loop;
v_criteria:=v_criteria || ')';
elsif rec_con.constraint_type = 'References' then
--v_criteria:=rec_con.r_constraint_name;
select table_name into v_fk_table_name
from all_constraints
where owner = v_schema_name
and constraint_name = rec_con.r_constraint_name;
v_temp:='(';
for rec_collist in curs_ccollist(v_schema_name,rec_con.r_constraint_name) loop
if v_temp != '(' then
v_temp:=v_temp||',';
end if;
v_temp:=v_temp || rec_collist.column_name;
end loop;
v_temp:=v_temp || ')';
v_criteria:=v_fk_table_name || v_temp || ' ' || rec_con.delete_rule;
elsif rec_con.constraint_type = 'Check' then
v_criteria:=rec_con.search_condition;
if length(v_criteria) > (250-32-14) then
v_criteria:=substr(v_criteria,1,(250-32-14)) || '...';
end if;
else
v_criteria:='Unknown';
end if;
n_exist:=n_exist+1;
dbms_output.put_line(rpad(rec_con.constraint_name,32) || chr(9) ||
rpad(rec_con.constraint_type,14) || chr(9) ||
v_criteria);
end loop;
if n_exist = 0 then
dbms_output.put_line('<none>');
end if;
-- indexes
dbms_output.put_line(chr(10) || rpad('INDEX',32) || chr(9) ||
rpad('TYPE',20) || chr(9) ||
'COLUMNS');
dbms_output.put_line(rpad('-----',32) || chr(9) ||
rpad('----',20) || chr(9) ||
'-------');
n_exist:=0;
for rec_ind in curs_inds (v_schema_name,v_table_name) loop
v_columns:='(';
for rec_collist in curs_icollist (v_schema_name,rec_ind.index_name) loop
if v_columns != '(' then
v_columns:=v_columns||',';
end if;
v_columns:=v_columns || rec_collist.column_name;
end loop;
v_columns:=v_columns || ')';
dbms_output.put_line(rpad(rec_ind.index_name,32) || chr(9) ||
rpad(rec_ind.uniqueness || ':' || rec_ind.index_type,20) || chr(9) ||
v_columns);
n_exist:=n_exist+1;
end loop;
if n_exist = 0 then
dbms_output.put_line('<none>');
end if;
-- triggers
dbms_output.put_line(chr(10) || rpad('TRIGGER',32) || chr(9) ||
rpad('TYPE',20) || chr(9) ||
rpad('EVENT',20) );
dbms_output.put_line(rpad('-----',32) || chr(9) ||
rpad('----',20) || chr(9) ||
rpad('------',20) );
n_exist:=0;
for rec_trig in curs_trigs (v_schema_name,v_table_name) loop
dbms_output.put_line(rpad(rec_trig.trigger_name,32) || chr(9) ||
rpad(rec_trig.trigger_type,20) || chr(9) ||
rpad(rec_trig.triggering_event,20) );
n_exist:=n_exist+1;
end loop;
if n_exist = 0 then
dbms_output.put_line('<none>');
end if;
-- some space before prompt
dbms_output.put_line(chr(10));
end if; -- check for table
end;
/
set feedback on