-- table_dependencies.sql
--
-- By Brian Peasland
-- 13 August 1999
-- 
-- This script is useful when a table needs to be dropped for some reason.
-- This script will show the table structure, any indexes on the table, 
-- any constraints on the table, any foreign key references to other tables,
-- and any foreign key references to this table.
-- By using this script, one can drop a table and recreate it with the
-- exact setup. This is useful when copying the table to another tablespace,
-- dropping columns, or importing/exporting the table in question.
--
-- Run this script in SQL*Plus with a DBA account.

set echo off
set verify off

prompt table_dependencies.sql
prompt 
accept xOwner prompt 'Enter table owner: '
accept xTable prompt 'Enter table: '
prompt
prompt Table Dependencies for &xOwner . &xTable
prompt

column OWNER format a20
column TABLE_NAME format a25
column TABLESPACE_NAME format a15
column TABLE_OWNER format a20
column COLUMN_NAME format a35

set linesize 130
set pagesize 60

prompt DESCRIPTION OF &xTable
column "Null?" format a8
column TYPE format a30
SELECT column_name AS Name,
DECODE (nullable,'N','Not Null') AS "Null?",
DECODE (data_type,'VARCHAR2',data_type||'('||data_length||')',
                  'NUMBER',data_type||'('||data_length||','||data_precision||')',
                  data_type) AS Type
FROM dba_tab_columns
WHERE owner=UPPER('&xOwner') AND table_name=UPPER('&xTable');

prompt PRIMARY KEY FOR &xTable
SELECT a.constraint_name AS pk_constraint_name,b.column_name,b.position
FROM dba_constraints a,dba_cons_columns b
WHERE a.constraint_type='P' AND a.owner=UPPER('&xOwner')
AND a.table_name=UPPER('&xTable') AND a.constraint_name=b.constraint_name
ORDER BY position;

prompt INDEXES ON &xTable

SELECT INDEX_NAME,UNIQUENESS,TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,
       MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE 
FROM dba_indexes
WHERE owner = UPPER('&xOwner') AND table_name=UPPER('&xTable')
ORDER BY index_name;

prompt INDEX COLUMNS FOR &xTable
SELECT index_name,column_name,column_position
FROM dba_ind_columns
WHERE index_owner=UPPER('&xOwner') AND index_name IN 
   (SELECT index_name FROM dba_indexes WHERE owner = UPPER('&xOwner')
    AND table_name=UPPER('&xTable'))
ORDER BY index_name,column_position;

prompt CHECK CONSTRAINTS FOR &xTable
column SEARCH_CONDITION format a40
column R_OWNER format a20
SELECT constraint_name,search_condition
FROM dba_constraints
WHERE owner=UPPER('&xOwner') AND table_name=UPPER('&xTable') AND constraint_type = 'C'
ORDER BY constraint_type,constraint_name;

prompt F.K. REFERENCES FOR &xTable
column DELETE_RULE format a11
SELECT constraint_name,r_constraint_name,delete_rule
FROM   dba_constraints
WHERE  owner=UPPER('&xOwner') AND table_name=UPPER('&xTable') AND constraint_type='R';

prompt TABLES &xTable REFERS TO
SELECT c.owner,c.table_name,c.constraint_name
FROM   dba_constraints c
WHERE  c.constraint_name IN (SELECT x.r_constraint_name
                             FROM dba_constraints x
                             WHERE x.owner=UPPER('&xOwner') 
                             AND   x.table_name=UPPER('&xTable') 
                             AND   x.constraint_type='R');

prompt F.K. REFERENCES TO &xTable
SELECT c.owner,c.table_name,c.constraint_name
FROM   dba_constraints c
WHERE  c.constraint_type='R' AND c.r_owner=UPPER('&xOwner') 
AND    c.r_constraint_name IN 
          (SELECT x.constraint_name 
           FROM   dba_constraints x
           WHERE  x.owner=UPPER('&xOwner') 
           AND    x.table_name=UPPER('&xTable') 
           AND    x.constraint_type='P');

prompt OTHER DEPENDENCIES ON &xTable
SELECT owner,name,type
FROM   dba_dependencies
WHERE  referenced_owner=UPPER('&xOwner') 
AND    referenced_name=UPPER('&xTable');