tabview.sql

/*
DESCRIPTION:
  This SQL*Plus report will produce a report of tables for a given
  database owner and table search string.  The report includes table
  name, columne name, column format, null indicator,
  foreign keys, unique and performace indices.
  The TABLE comment is included in the first row if is not null.
  This report was designed to be imported into Word.  Set the font
  to CARRIER NEW 6, page layout to landscape, set up 2 newpaper columns
  and adjust the margins so that the header appears at the top of each
  page.


INPUT:   1) Database owner (No Wild Cards) i.e. SCOTT
         2) Table Name search criteria.  Wild Cards are allowed.
            Use % for all tables.

OUTPUT:  TABVIEW.LIS

AUTHOR:  Mark Hazleton (mark.hazleton@projectmechanics.com)
         With help from existing SQL scripts from various sources.
*/
SET ECHO OFF
SET VERIFY OFF

PROMPT Enter owner name to report on (NO WILD CARDS)
accept IN_OWNER PROMPT 'Owner:   '
PROMPT Enter table search string (WILD CARDS OK)
accept IN_TABLE PROMPT 'Tables:  '

SET TERMOUT OFF
SET PAUSE OFF
SET PAGESIZE 75
SET LINESIZE 81
SET NEWPAGE 1
SET FEEDBACK OFF
SET SPACE 1

COLUMN  RUN_DATE NEW_VALUE RPT_DATE NOPRINT                          ;

SELECT  SYSDATE RUN_DATE FROM SYS.DUAL                               ;

TTITLE LEFT    'Run Date: 'RPT_DATE                                  -
       CENTER  'TABVIEW for '&&IN_OWNER '/' &&IN_TABLE               -
       RIGHT   'Page: ' SQL.PNO format 999                           -
       SKIP    1                                                     ;
BTITLE SKIP 1                                                        -
       LEFT 'T-TABLE  V-VIEW  P-PRIMARY KEY I-INDEX F-FOREIGN KEY '  ;

BREAK  ON C1 SKIP 2                                                  -
       ON C4 SKIP 1 NODUP                                            -
       ON C5 NODUP                                                   ;

COLUMN C1  NOPRINT                                                   ;
COLUMN C2  NOPRINT                                                   ;
COLUMN C3  NOPRINT                                                   ;
COLUMN C4  FORMAT A1  HEADING ' '                                    ;
COLUMN C5  FORMAT A30 HEADING 'Table/Index Name'WORD_WRAPPED         ;
COLUMN C6  FORMAT A25 HEADING 'Column Name'     WORD_WRAPPED         ;
COLUMN C7  FORMAT A5  HEADING 'Null?'                                ;
COLUMN C8  FORMAT A14 HEADING 'Type'                                 ;

SPOOL   c:\winnt\temp\TABVIEW.LIS

SELECT  TAB.TABLE_NAME          C1,
        0                       C2,
        0                       C3,
        DECODE(TAB.TABLE_TYPE,
                'TABLE','T',
                'VIEW' ,'V',
                ''
              )                 C4,
        TAB.TABLE_NAME          C5,
        COM.COMMENTS            C6,
        ''                      C7,
        '(COMMENT)'             C8
FROM    ALL_TAB_COMMENTS COM,
        ALL_CATALOG TAB
WHERE   TAB.OWNER         = '&&IN_OWNER'
AND     TAB.TABLE_NAME LIKE '&&IN_TABLE'
AND     TAB.OWNER         = COM.OWNER
AND     TAB.TABLE_NAME    = COM.TABLE_NAME
AND     COM.COMMENTS IS NOT NULL
UNION
SELECT  TAB.TABLE_NAME          C1,
        COL.COLUMN_ID           C2,
        1                       C3,
        DECODE(TAB.TABLE_TYPE,
                'TABLE','T',
                'VIEW' ,'V',
                ''
              )                 C4,
        COL.TABLE_NAME          C5,
        COL.COLUMN_NAME         C6,
        DECODE(COL.NULLABLE,
                'Y',' ',
                'N','!Null',
                ' '
              )                 C7,
        COL.DATA_TYPE                                                     ||
           DECODE(COL.DATA_TYPE,'DATE','','(')                            ||
           DECODE(COL.DATA_TYPE,
                         'CHAR',COL.DATA_LENGTH,
                     'VARCHAR2',COL.DATA_LENGTH,
                       'NUMBER',NVL(COL.DATA_PRECISION,COL.DATA_LENGTH),
                                ''
                 )                                                        ||
           DECODE(COL.DATA_TYPE,
                       'NUMBER',DECODE(NVL(COL.DATA_SCALE,0),
                                                    0,'',
                                                      ','
                                      ),
                                ''
                     )                                                    ||
           DECODE(COL.DATA_TYPE,
                       'NUMBER',DECODE(NVL(COL.DATA_SCALE,0),
                                                    0,'',
                                                      COL.DATA_SCALE
                                      ),
                                ''
                     )                                                    ||
           DECODE(COL.DATA_TYPE,
                         'DATE','',
                                ')'
                 )         C8
FROM    ALL_TAB_COLUMNS COL,
        ALL_CATALOG TAB
WHERE   TAB.OWNER         = '&&IN_OWNER'
AND     TAB.TABLE_NAME LIKE '&&IN_TABLE'
AND     TAB.OWNER         = COL.OWNER
AND     TAB.TABLE_NAME    = COL.TABLE_NAME
UNION
SELECT  AIN.TABLE_NAME                                              C1,
        IND.COLUMN_POSITION                                         C2,
        3                                                           C3,
        'I'                                                         C4,
        AIN.INDEX_NAME ||DECODE(AIN.UNIQUENESS,'UNIQUE','(U)','  ') C5,
        IND.COLUMN_NAME                                             C6,
        ' '                                                         C7,
        ' '                                                         C8
FROM    ALL_IND_COLUMNS IND,
        ALL_INDEXES AIN
WHERE   AIN.TABLE_OWNER    = '&&IN_OWNER'
AND     AIN.TABLE_NAME  LIKE '&&IN_TABLE'
AND     AIN.TABLE_OWNER    = IND.TABLE_OWNER
AND     AIN.TABLE_NAME     = IND.TABLE_NAME
AND     AIN.INDEX_NAME     = IND.INDEX_NAME
UNION
SELECT AC1.TABLE_NAME                      C1,
       CC1.POSITION                        C2,
       4                                   C3,
       'F'                                 C4,
       AC2.TABLE_NAME                      C5,
       CC1.COLUMN_NAME                     C6,
       ' '                                 C7,
       ' '                                 C8
  FROM ALL_CONSTRAINTS  AC1,
       ALL_CONSTRAINTS  AC2,
       ALL_CONS_COLUMNS CC1
 WHERE AC1.OWNER            = '&&IN_OWNER'
   AND AC1.TABLE_NAME    LIKE '&&IN_TABLE'
   AND AC1.CONSTRAINT_TYPE  = 'R'
   AND AC2.OWNER           = '&&IN_OWNER'
   AND AC2.CONSTRAINT_TYPE = 'P'
   AND AC2.CONSTRAINT_NAME = (SELECT R_CONSTRAINT_NAME
                               from all_constraints
                              WHERE OWNER           = AC1.OWNER
                                AND TABLE_NAME      = AC1.TABLE_NAME
                                AND CONSTRAINT_NAME = AC1.CONSTRAINT_NAME)
   AND AC1.OWNER = CC1.OWNER
   AND AC1.CONSTRAINT_NAME = CC1.CONSTRAINT_NAME
ORDER BY 1,3,5,2
;
SPOOL OFF;
SET VERIFY ON  ;
SET FEEDBACK ON;
SET TERMOUT ON ;
SET ECHO ON    ;
select 'Please see c:\windows\temp\tabview.lis' from dual;