REM  #*****************************************************
REM  # File Name: info.sql
REM  #
REM  # Purpose: Dumps out the columns, format and indexes, 
REM  #          for specified table and whether unique etc
REM  #
REM  #*****************************************************

set linesize 300
set verify off
set serveroutput on size 1000000

col NAME heading Name
col NULLOK heading Null? format a8
col TYPE heading Type format a15
col COMMENTS heading Comments format a80 WORD_WRAP
col DEFVAL heading 'Default' format a20


set heading off

select 'Object Name: ' || table_name from all_tab_comments where table_name=upper('&1') union
select 'Owner      : ' || owner from all_tab_comments where table_name=upper('&1') union
select 'Object Type: ' || object_type from all_objects where object_name=upper('&1') and (object_type='VIEW' or object_type='TABLE');

set heading on

select comments from all_tab_comments where table_name=upper('&1');

col COMMENTS format a80

select  	t.column_name Name, 
		decode(t.Nullable, 'Y','', 'N', 'NOT NULL') NullOK,
			decode (t.data_type,
			'NUMBER'  , decode (t.data_precision, 
					'',      'NUMBER',
						 'NUMBER' || '(' || t.data_precision || ','|| t.data_scale || ')' ), 
			'DATE'    ,'DATE',
			'VARCHAR2','VARCHAR2' || '(' || t.data_length || ')' 
			) Type,
		t.Data_default DEFVAL,
		c.comments Comments
from    	all_col_comments c , 
		all_tab_columns t 
where  	 	t.table_name = c.table_name
and    	 	t.owner = c.owner
and   	 	t.column_name = c.column_name
and     	t.table_name = upper('&1')
order by 	t.column_id;

set feedback off
prompt
prompt
prompt Indexes on table &1
prompt

DECLARE
  CURSOR ind_col IS
    SELECT table_owner, table_name, index_name, column_name
    FROM   all_ind_columns
    WHERE  
      (table_name  like upper('&1') or table_name  like 'TDM_'||upper('&1'))
    AND    column_name != 'TDM_USER_ID'
    ORDER BY table_owner, table_name, DECODE(SUBSTR(index_name,1,2),'PK','0',index_name) asc, column_position    ;

    ind_col_rec 	ind_col%ROWTYPE;
    tmp_uniqueness	varchar(30);
    tmp_status		varchar(11);
    tmp_distinct_keys	number;
    spaces		varchar(50) := '                                            ';
    last_owner		varchar(30);
    last_table_name	varchar(30) := 'x';
    last_index_name	varchar(30);
    first_loop		CHAR(1) := 'Y';

BEGIN
  OPEN ind_col;
  FETCH ind_col INTO ind_col_rec;
  IF ind_col%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('--> No Indexes Found.');
    GOTO finish;
  END IF;
--  DBMS_OUTPUT.PUT_LINE('>');  
  DBMS_OUTPUT.PUT_LINE('> '||
--                       substr('Table Owner'||spaces,1,14)||
--                       substr('Table Name' ||spaces,1,30)||
                       substr('Index Name' ||spaces,1,35)||
                       substr('Unique?'    ||spaces,1,12)||
                       substr('Column Name'||spaces,1,30)
                       );
  DBMS_OUTPUT.PUT_LINE('> '||
--                       substr('-----------'||spaces,1,14)||
--                       substr('-----------'||spaces,1,30)||
                       substr('----------------------------------'||spaces,1,35)||
                       substr('-----------'||spaces,1,12)||
                       substr('----------------------------------'||spaces,1,30)
                       );


  LOOP
    EXIT WHEN ind_col%NOTFOUND;

    IF first_loop = 'Y' THEN
      first_loop := 'N';
      last_table_name := ind_col_rec.table_name;
    ELSIF ind_col_rec.table_name != last_table_name  THEN
      DBMS_OUTPUT.PUT_LINE('>');
      DBMS_OUTPUT.PUT_LINE('> -------------------------------------------------------------------------');
      last_table_name := ind_col_rec.table_name;
    END IF;

    IF ind_col_rec.index_name = last_index_name THEN
      DBMS_OUTPUT.PUT_LINE('> '||
--                           substr('>'||spaces,1,14)||
--                           substr(spaces,1,30)||
                           substr(spaces,1,35)||
                           substr(spaces,1,12)||
                           ind_col_rec.column_name
                           );

    ELSE last_index_name := ind_col_rec.index_name;
      SELECT uniqueness, status, distinct_keys
      INTO   tmp_uniqueness, tmp_status, tmp_distinct_keys
      FROM   all_indexes
      WHERE  table_name  = ind_col_rec.table_name
        AND  table_owner = ind_col_rec.table_owner
        AND  index_name  = ind_col_rec.index_name;
      DBMS_OUTPUT.PUT_LINE('>');
      DBMS_OUTPUT.PUT_LINE('> '||
--                           substr(ind_col_rec.table_owner||spaces,1,14)||
--                           substr(ind_col_rec.table_name ||spaces,1,30)||
                           substr(ind_col_rec.index_name ||spaces,1,35)||
                           substr(tmp_uniqueness         ||spaces,1,12)||
                           ind_col_rec.column_name
                           );
    END IF;

    FETCH ind_col INTO ind_col_rec;
  END LOOP;
  <<finish>>
  CLOSE ind_col;
END;
/
prompt
set feedback on
set verify on