REM This script takes table name or part of table name and generates a report of
REM 1. table structure
REM 2. indexes


set feedback off;
Set veri off;
set serveroutput on;
spool fulldef.log;
prompt 0. List Tables Only
prompt 1. 0 + List Column Definition
prompt 2. 1 + List Index Definition
prompt

declare
cursor tabs is
select a.table_name, a.owner,
to_char(created,'DD-MON-YYYY') created,
to_char(last_ddl_time,'DD-MON-YYYY')
last_ddl_time
from all_tables a, all_tab_comments c, all_objects o
where a.table_name = c.table_name
and a.table_name = o.object_name
and a.table_name like upper('&tab_name%')
and o.object_type = 'TABLE'
and a.owner <> 'SYS';

cursor cols(tab_name varchar2) is
select column_name, data_type, data_length, data_precision,
decode(nullable,'Y','NULL','N','NOT NULL')
nullable
from all_tab_columns
where table_name = tab_name;

cursor inds(tab_name varchar2) is
select i.index_name, column_name, uniqueness
from all_indexes i, all_ind_columns c
where i.index_name = c.index_name
and i.table_name = tab_name
order by i.index_name;

ruler char(80);
star char(80);
tab_ctr number;
opt number;
v_prev_index_name Varchar2(30);
v_count number;
fetch_val integer;
l_cursor integer;
l_ret_value integer;
v_sql_stmt varchar2(2000);
begin
dbms_output.enable(100000);
ruler :=
'---------------------------------------------------------------------------
----';
star :=
'***************************************************************************
****';
tab_ctr := 0;
opt := &option;

--
-- Print column heading for table list only
--
if opt = 0 then
dbms_output.put_line( lpad(' ',30,'-') || lpad(' ',10,'-')
|| lpad(' ',15,'-') || lpad(' ',15,'-') || lpad(' ',10,'-'));
dbms_output.put_line( rpad('TABLE',30,' ') ||
rpad('OWNER',10,' ') || rpad('CREATED',15,' ') || rpad('LAST DDL',15,' ') ||
rpad('Records#',10,' '));
dbms_output.put_line( lpad(' ',30,'-') || lpad(' ',10,'-')
|| lpad(' ',15,'-') || lpad(' ',15,'-') || lpad(' ',10,'-'));

end if;
for tabs_set in tabs loop
--
-- Table Information
--
v_sql_stmt := 'Select count(*) from ' || tabs_set.table_name
;
l_cursor := dbms_sql.open_cursor;
dbms_sql.parse (l_cursor, v_sql_stmt, dbms_sql.v7);
dbms_sql.define_column (l_cursor, 1, v_count);

l_ret_value := dbms_sql.execute (l_cursor);
fetch_val := dbms_sql.fetch_rows (l_cursor);
dbms_sql.column_value (l_cursor, 1, v_count);
dbms_sql.close_cursor(l_cursor);

if opt = 0 then
dbms_output.put_line( rpad(tabs_set.table_name,30,'
') || rpad(tabs_set.owner,10,' ') || rpad(tabs_set.created,15,' ') ||
rpad(tabs_set.last_ddl_time,15,' ') || rpad(to_char(v_count),10,' '));
else
dbms_output.put_line (star);
dbms_output.put_line('Table : ' ||
tabs_set.table_name);
dbms_output.put_line('Owner : ' ||
tabs_set.owner);
dbms_output.put_line('Created : ' ||
tabs_set.created);
dbms_output.put_line('Last DDL : ' ||
tabs_set.last_ddl_time);
dbms_output.put_line('Records : ' ||
to_char(v_count));
end if;

if opt > 0 then
--
-- Columns
--
dbms_output.put_line (ruler);
dbms_output.put_line ('--- ');
dbms_output.put_line ('--- Columns');
dbms_output.put_line ('--- ');
dbms_output.put_line (lpad(' ',30,'-') || lpad(' ',10,'-')||
lpad(' ',15,'-') || lpad(' ',10,'-') || lpad(' ',15,'-'));
dbms_output.put_line (rpad('COLUMN',30,' ') ||
rpad('NULLABLE',10,' ')|| rpad('TYPE',15,' ') || rpad('LENGTH',10,' ') ||
rpad('PRECISION',15,' '));
dbms_output.put_line (lpad(' ',30,'-') || lpad(' ',10,'-')||
lpad(' ',15,'-') || lpad(' ',10,'-') || lpad(' ',15,'-'));
for cols_set in cols(tabs_set.table_name) loop
dbms_output.put_line (rpad(cols_set.column_name,30,'
') || rpad(cols_set.nullable,10,' ') || rpad(cols_set.data_type,15,' ') ||
rpad(to_char(cols_set.data_length),10,' ') ||
rpad(cols_set.data_precision,15,' '));
end loop; -- Column Cursor
end if;

if opt > 1 then
--
-- Index Definition
--
v_prev_index_name := 'NULL';
for inds_set in inds(tabs_set.table_name) loop
if (inds%rowcount = 1) then
dbms_output.put_line (ruler);
dbms_output.put_line ('--- ');
dbms_output.put_line ('--- Indexes ');

dbms_output.put_line ('--- ');
dbms_output.put_line (rpad(' ',30,'-') ||
rpad(' ',30,'-') || rpad(' ',15,'-'));
dbms_output.put_line (rpad('COLUMN',30,' ')
|| rpad('INDEX',30,' ') || rpad('UNIQUENESS',15,' '));
dbms_output.put_line (rpad(' ',30,'-') ||
rpad(' ',30,'-') || rpad(' ',15,'-'));
end if;
if (v_prev_index_name <> inds_set.index_name) then
dbms_output.put_line
(rpad(inds_set.column_name,30,' ') || rpad(inds_set.index_name,30,' ') ||
rpad(inds_set.uniqueness,15,' '));
else
dbms_output.put_line (inds_set.column_name);
end if;
v_prev_index_name := inds_set.index_name;
end loop; -- Index Cursor
end if;


tab_ctr := tab_ctr + 1;
end loop; -- Table Cursor

if tab_ctr = 0 then
dbms_output.new_line ;
dbms_output.put_line ('No matching tables found');

else
if opt = 0 then
dbms_output.put_line( lpad(' ',30,'-') || lpad('
',10,'-') || lpad(' ',15,'-') || lpad(' ',15,'-') || lpad(' ',10,'-'));
else
dbms_output.put_line (star);
end if;
end if;
end;
/

prompt
spool off;
set feedback on;
set veri on;