all_tab.sql

rem  ======================================================
rem  Script to identify everything to do with a table.
rem 
rem  This includes a DESC equivalent, sizing information, Triggers, Constraints, Granted priviliges
rem  that are associated with a table or from other tables foreign keys
rem  that reference that table.
rem 
rem  Instructions
rem  ============
rem  Either run this script logged on to SYS or SYSTEM or GRANT SELECT on
rem  the DICTIONARY TABLES:
rem 
rem  DBA_TAB_COLUMNS
rem  V$DATABASE
rem  DBA_TABLES
rem  DBA_EXTENTS
rem  DBA_CONS_COLUMNS
rem  DBA_CONSTRAINTS
rem  DBA_TRIGGERS
rem  DBA_TAB_PRIVS
rem  DBA_COL_PRIVS
rem 
rem  At SQL*PLUS You will be requested to enter the schema owner and the tablename.
rem  If you want a count of the number of rows in that table you will need to manually
rem  edit this file beforehand.
rem 
rem 
rem ======================================================

rem  ================================================
rem 
rem  INSTRUCTIONS FOR MODIFICATION
rem  =============================
rem 
rem  SHOW THE DATABASE AND USER THAT YOU ARE LOGGED ONTO.
rem  ========================================================

SELECT * FROM SYS.V_$DATABASE;

SHOW USER

SET ECHO ON FEED ON  ARRAYSIZE 1  LONG 5000 VERIFY OFF TIMING ON

rem  GET ALL TABLE DETAILS
rem  =====================
rem  =====================

rem  If ANALYZE STATISTICS has not been run then count number of rows
rem  in table.
rem  =====================================================================

rem O N L Y   E D I T   I F   N E E D   T H E   C O U N T (*)
rem ??????????????????????????????????????????????????????

rem  SELECT COUNT(*) FROM OPS$ISSPOWN.STANDARD_TEST_RESULT;


rem  Show the Table Structure
rem  ========================

COLUMN POS FORMAT 999 heading "POS"
COLUMN PCT_FREE FORMAT A4 heading "Null"


SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE, COLUMN_ID POS
FROM   SYS.DBA_TAB_COLUMNS
WHERE  OWNER = upper('&&owner')
AND    TABLE_NAME = upper('&&table')
ORDER  BY COLUMN_ID;



rem  Show Physical Attributes
rem  ========================
COLUMN PCT_FREE FORMAT 999 heading "%|Free"
COLUMN PCT_INCREASE FORMAT 999 heading "%|Incr"
COLUMN INITIAL_EXTENT FORMAT 999999999 heading "Init|Extent"
COLUMN NEXT_EXTENT FORMAT    9999999999999 heading "Next|Extent"
COLUMN MAX_EXTENTS FORMAT 999 heading "Max|Ext"
COLUMN AVG_ROW_LEN FORMAT 99999 heading "Avg|Row|Len"

SELECT PCT_FREE,
       PCT_INCREASE,
       INITIAL_EXTENT,
       NEXT_EXTENT,
       MAX_EXTENTS,
       NUM_ROWS,
       AVG_ROW_LEN
FROM   SYS.DBA_TABLES
WHERE  OWNER = upper('&&owner')
AND    TABLE_NAME = upper('&&table');


rem  Show the actual Maximum Size of a Row
rem  ==============================

SELECT SUM(DATA_LENGTH)
FROM   SYS.DBA_TAB_COLUMNS
WHERE  OWNER = upper('&&owner')
AND    TABLE_NAME = upper('&&table');


rem  Show the Number of Physical EXTENTS that have been allocated
Attributes
rem  ========================================================

COLUMN SEGMENT_NAME FORMAT A30 HEADING 'Table Name'
COLUMN COUNTER FORMAT 9999 HEADING 'Number Of Extents Used'

SELECT SEGMENT_NAME, COUNT(*) COUNTER
FROM   SYS.DBA_EXTENTS
WHERE  OWNER = upper('&&owner')
AND    SEGMENT_NAME = upper('&&table')
GROUP  BY SEGMENT_NAME;



COLUMN TABSIZE FORMAT 999999999999 HEADING 'Table Size In Bytes'

rem  Show the Physical SIZE IN BYTES of the TABLE
rem  =====================================

SELECT SEGMENT_NAME, SUM(BYTES) TABSIZE
FROM   SYS.DBA_EXTENTS
WHERE  OWNER = upper('&&owner')
AND    SEGMENT_NAME = upper('&&table')
GROUP  BY SEGMENT_NAME;




rem  GET ALL THE INDEX DETAILS
rem  =========================
rem  =========================


rem  Show all the indexes and their columns for this table
rem  =====================================================

COLUMN OWNER FORMAT A8 heading "Index|Owner"
COLUMN TABLE_OWNER FORMAT A8 heading "Table|Owner"
COLUMN INDEX_NAME FORMAT A30 heading "Index Name"
COLUMN COLUMN_NAME FORMAT A30 heading "Column Name"
COLUMN COLUMN_POSITION FORMAT 9999 heading "Pos"
BREAK ON CONSTRAINT_NAME SKIP PAGE

SELECT IND.OWNER,
       IND.TABLE_OWNER,
       IND.INDEX_NAME,
       IND.UNIQUENESS,
       COL.COLUMN_NAME,
       COL.COLUMN_POSITION
FROM   SYS.DBA_INDEXES IND,
       SYS.DBA_IND_COLUMNS COL
WHERE  IND.TABLE_NAME = upper('&&table')
AND    IND.TABLE_OWNER = upper('&&owner')
AND    IND.TABLE_NAME = COL.TABLE_NAME
AND    IND.OWNER = COL.INDEX_OWNER
AND    IND.TABLE_OWNER = COL.TABLE_OWNER
AND    IND.INDEX_NAME = COL.INDEX_NAME;

rem  Display all the physical details of the Primary and Other
rem  Indexes for this table
rem  =========================================================
COLUMN OWNER FORMAT A8 heading "Index|Owner"
COLUMN TABLE_OWNER FORMAT A8 heading "Table|Owner"
COLUMN INDEX_NAME FORMAT A30 heading "Index Name"
COLUMN COLUMN_NAME FORMAT A30 heading "Column Name"
COLUMN COLUMN_POSITION FORMAT 9999 heading "Pos"
COLUMN PCT_FREE FORMAT 999 heading "%|Free"
COLUMN PCT_INCREASE FORMAT 999 heading "%|Incr"
COLUMN INITIAL_EXTENT FORMAT 999999999 heading "Init|Extent"
COLUMN NEXT_EXTENT FORMAT 999999999 heading "Next|Extent"
COLUMN MAX_EXTENTS FORMAT 999 heading "Max|Ext"

SELECT
IND.OWNER,
IND.TABLE_OWNER,
IND.INDEX_NAME,
IND.UNIQUENESS,
COL.COLUMN_NAME,
COL.COLUMN_POSITION,
IND.PCT_FREE,
IND.PCT_INCREASE,
IND.INITIAL_EXTENT,
IND.NEXT_EXTENT,
IND.MAX_EXTENTS
FROM DBA_INDEXES IND,
     DBA_IND_COLUMNS COL
WHERE IND.TABLE_NAME = upper('&&table')
AND IND.TABLE_OWNER = upper('&&owner')
AND IND.TABLE_NAME = COL.TABLE_NAME
AND IND.OWNER = COL.INDEX_OWNER
AND IND.TABLE_OWNER = COL.TABLE_OWNER
AND IND.INDEX_NAME = COL.INDEX_NAME;

rem 
rem  GET ALL THE CONSTRAINT DETAILS
rem  ==============================
rem  ==============================

rem  Show the Non-Foreign Keys Constraints on this table
rem  ====================================================================
COLUMN OWNER FORMAT A9 heading "Owner"
COLUMN CONSTRAINT_NAME FORMAT A30 heading "Constraint|Name"
COLUMN R_CONSTRAINT_NAME FORMAT A30 heading "Referenced|Constraint|Name"

COLUMN DELETE_RULE FORMAT A9 heading "Del|Rule"
COLUMN TABLE_NAME FORMAT A18 heading "Table Name"
COLUMN COLUMN_NAME FORMAT A30 heading "Column Name"
rem COLUMN CONSTRAINT_TYPE FORMAT A4 heading "Type"
rem COLUMN POSITION ALIAS POS
rem COLUMN POSITION 9999 heading "Pos"
COLUMN POSITION FORMAT 9999 heading "Pos"
BREAK ON CONSTRAINT_NAME SKIP PAGE



SELECT COL.OWNER,
       COL.CONSTRAINT_NAME,
       COL.COLUMN_NAME,
       COL.POSITION,
rem      CON.CONSTRAINT_TYPE
DECODE (CON.CONSTRAINT_TYPE,
       'P','primary','R','foreign','U','unique','C','check') "Type"
FROM   DBA_CONS_COLUMNS COL,
       DBA_CONSTRAINTS CON
WHERE  COL.OWNER = upper('&&owner')
AND    COL.TABLE_NAME = upper('&&table')
AND    CONSTRAINT_TYPE <> 'R'
AND    COL.OWNER = CON.OWNER
AND    COL.TABLE_NAME = CON.TABLE_NAME
AND    COL.CONSTRAINT_NAME = CON.CONSTRAINT_NAME
ORDER BY COL.CONSTRAINT_NAME, COL.POSITION;


rem  Show the Foreign Keys on this table pointing at other tables Primary
rem  Key Fields for referential Integrity purposes.
rem  ====================================================================


SELECT CON.OWNER,
       CON.TABLE_NAME,
       CON.CONSTRAINT_NAME,
       CON.R_CONSTRAINT_NAME,
       CON.DELETE_RULE,
       COL.COLUMN_NAME,
       COL.POSITION,
rem      CON1.OWNER,
       CON1.TABLE_NAME "Ref Tab",
       CON1.CONSTRAINT_NAME "Ref Const"
rem      COL1.COLUMN_NAME "Ref Column",
rem      COL1.POSITION
rem FROM   DBA_CONS_COLUMNS COL,
FROM   DBA_CONSTRAINTS CON1,
       DBA_CONS_COLUMNS COL,
       DBA_CONSTRAINTS CON
WHERE  CON.OWNER = upper('&&owner')
AND    CON.TABLE_NAME = upper('&&table')
AND    CON.CONSTRAINT_TYPE = 'R'
AND    COL.OWNER = CON.OWNER
AND    COL.TABLE_NAME = CON.TABLE_NAME
AND    COL.CONSTRAINT_NAME = CON.CONSTRAINT_NAME
rem  Leave out next line if looking for other Users with Foriegn Keys.
AND    CON1.OWNER = CON.OWNER
AND    CON1.CONSTRAINT_NAME = CON.R_CONSTRAINT_NAME
AND    CON1.CONSTRAINT_TYPE IN ( 'P', 'U' );
rem  The extra DBA_CONS_COLUMNS will give details of refered to columns,
rem  but has a multiplying effect on the query results.
rem  NOTE: Could use temporary tables to sort out.
rem AND    COL1.OWNER = CON1.OWNER
rem AND    COL1.TABLE_NAME = CON1.TABLE_NAME
rem AND    COL1.CONSTRAINT_NAME = CON1.CONSTRAINT_NAME;



rem  Show the Foreign Keys pointing at this table via the recursive call
rem  to the Constraints table.
rem  ================================================================

SELECT CON1.OWNER,
       CON1.TABLE_NAME,
       CON1.CONSTRAINT_NAME,
       CON1.DELETE_RULE,
       CON1.STATUS,
       CON.TABLE_NAME,
       CON.CONSTRAINT_NAME,
       COL.POSITION,
       COL.COLUMN_NAME
FROM   DBA_CONSTRAINTS CON,
       DBA_CONS_COLUMNS COL,
       DBA_CONSTRAINTS CON1
WHERE  CON.OWNER = upper('&&owner')
AND    CON.TABLE_NAME = upper('&&table')
AND    ((CON.CONSTRAINT_TYPE = 'P') OR (CON.CONSTRAINT_TYPE = 'U'))
AND    COL.TABLE_NAME = CON1.TABLE_NAME
AND    COL.CONSTRAINT_NAME = CON1.CONSTRAINT_NAME
AND    CON1.OWNER = CON.OWNER
AND    CON1.R_CONSTRAINT_NAME = CON.CONSTRAINT_NAME
AND    CON1.CONSTRAINT_TYPE = 'R'
GROUP BY CON1.OWNER,
         CON1.TABLE_NAME,
         CON1.CONSTRAINT_NAME,
         CON1.DELETE_RULE,
         CON1.STATUS,
         CON.TABLE_NAME,
         CON.CONSTRAINT_NAME,
         COL.POSITION,
         COL.COLUMN_NAME;



rem 
rem  Show all the check Constraints
rem  ==========================================================

SET  HEADING OFF

SELECT 'alter table ', TABLE_NAME, ' add constraint ',
        CONSTRAINT_NAME, ' check ( ', SEARCH_CONDITION, ' ); '
FROM DBA_CONSTRAINTS
WHERE OWNER = upper('&&owner')
AND TABLE_NAME = upper('&&table')
AND CONSTRAINT_TYPE = 'C';

rem 
rem  Show all the Triggers that have been created on this table
rem  ==========================================================

rem  add query to extract Trigger Body etcc WHEN CLAUSE here.

SET ARRAYSIZE 1
SET LONG 6000000


SELECT OWNER,
'CREATE OR REPLACE TRIGGER ',
       TRIGGER_NAME,
       DESCRIPTION,
       TRIGGER_BODY,
       '/'
FROM  DBA_TRIGGERS
WHERE OWNER = upper('&&owner')
AND   TABLE_NAME = upper('&&table');



rem 
rem  Show all the GRANTS made on this table and it's columns.
rem  ========================================================


rem  Table 1st
rem  =========
SELECT 'GRANT ',
        PRIVILEGE,
      ' ON ',
        TABLE_NAME,
      ' TO ',
        GRANTEE,
       ';'
FROM DBA_TAB_PRIVS
WHERE OWNER = upper('&&owner')
AND   TABLE_NAME = upper('&&table');

rem  Columns 2nd
rem  ===========

SELECT 'GRANT ',
        PRIVILEGE,
      ' ( ',
        COLUMN_NAME,
      ' ) ',
      ' ON ',
        TABLE_NAME,
      ' TO ',
        GRANTEE,
       ';'
FROM DBA_COL_PRIVS
WHERE OWNER = upper('&&owner')
AND   TABLE_NAME = upper('&&table');

SET  HEADING ON

rem EXIT