prompt --------CUT-----------------------------CUT-----------------------CUT-----------------------------CUT---------------------
set verify off AUTOTRACE OFF TIMING OFF linesize 200
COLUMN COMMENTS FORMAT A50
COLUMN column_name FORMAT A35
COLUMN Data_Type FORMAT A15
COLUMN DATA_DEFAULT FORMAT A20
COLUMN "PK Column" FORMAT A35
COLUMN "FK Column" FORMAT A20
UNDEF Owner
ACCEPT Owner PROMPT 'Enter Owner :'
UNDEF Table_Name
ACCEPT Table_Name PROMPT 'Enter Table Name :'
--define Table_Name='xxxx'
--define Owner='XXXX'
SET HEADING OFF
PROMPT
PROMPT Comments for Table &Table_Name.
SELECT COMMENTS FROM ALL_TAB_COMMENTS
WHERE TABLE_NAME = UPPER('&Table_Name')
AND Owner = UPPER('&Owner') ;
SET HEADING ON
PROMPT
PROMPT Column Details for Table &Table_Name.
SELECT
ROWNUM "Sr No", T.COLUMN_NAME , T.Data_Type , T.DATA_LENGTH,DECODE(T.Nullable, 'N' , 'NOT NULL' , 'Y', ' ') NULLABLE , T.Data_Default , C.Comments FROM ALL_TAB_COLS T , All_Col_Comments C
WHERE 1=1
and T.OWNER = C.OWNER
AND T.TABLE_NAME = C.TABLE_NAME
AND T.COLUMN_NAME = C.COLUMN_NAME
AND T.TABLE_NAME = UPPER('&Table_Name')
AND T.Owner = UPPER('&Owner') ;
PROMPT
PROMPT PRIMARY KEY for Table &Table_Name.
select COLUMN_NAME FROM ALL_CONS_COLUMNS
WHERE TABLE_NAME = UPPER('&Table_Name.')
AND Owner = UPPER('&Owner.')
AND CONSTRAINT_NAME = ( SELECT CONSTRAINT_NAME
FROM ALL_CONSTRAINTS
WHERE TABLE_NAME = UPPER('&Table_Name')
AND CONSTRAINT_TYPE = 'P'
AND Owner = UPPER('&Owner')
)
ORDER BY POSITION
/
PROMPT
PROMPT INDEXES for Table &Table_Name.
BREAK ON INDEX_NAME ON UNIQUENESS SKIP 1
SELECT I.INDEX_NAME , C.COLUMN_NAME , I.UNIQUENESS FROM ALL_IND_COLUMNS C , ALL_INDEXES I WHERE C.INDEX_NAME = I.INDEX_NAME
AND C.TABLE_NAME = I.TABLE_NAME
AND I.TABLE_NAME = UPPER('&Table_Name')
AND I.Owner = UPPER('&Owner')
AND C.Table_Owner = UPPER('&Owner')
AND NOT EXISTS ( SELECT 'X' FROM ALL_CONSTRAINTS WHERE CONSTRAINT_NAME = I.INDEX_NAME
AND Owner = UPPER('&Owner.')
)
ORDER BY INDEX_NAME , COLUMN_POSITION
/
CLEAR BREAKS
PROMPT
PROMPT FOREIGN KEYS for Table &Table_Name.
BREAK ON CONSTRAINT_NAME ON TABLE_NAME ON R_CONSTRAINT_NAME SKIP 1
COLUMN POSITION NOPRINT
SELECT UNIQUE A.CONSTRAINT_NAME,C.COLUMN_NAME "FK Column" ,B.TABLE_NAME || '.' || B.COLUMN_NAME "PK Column",A.R_CONSTRAINT_NAME , C.POSITION
FROM ALL_CONSTRAINTS A, ALL_CONS_COLUMNS B, ALL_CONS_COLUMNS C
WHERE A.R_CONSTRAINT_NAME=B.CONSTRAINT_NAME
AND B.OWNER=UPPER('&OWNER')
AND A.CONSTRAINT_NAME=C.CONSTRAINT_NAME
AND A.OWNER=C.OWNER
AND A.OWNER = B.OWNER
AND A.TABLE_NAME=C.TABLE_NAME
AND B.POSITION=C.POSITION
AND A.TABLE_NAME LIKE UPPER('&TABLE_NAME')
ORDER BY A.CONSTRAINT_NAME, C.POSITION
/
set echo off
Set pages 999
set long 90000
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT',UPPER('&Table_Name'),UPPER('&Owner')) from dual
/
select dbms_metadata.get_ddl('TABLE',UPPER('&Table_Name'),UPPER('&Owner') ) from dual
/
COLUMN POSITION NOPRINT
CLEAR BREAKS
PROMPT
PROMPT CONSTRAINTS for Table &Table_Name.
SELECT CONSTRAINT_NAME , SEARCH_CONDITION FROM ALL_CONSTRAINTS
WHERE TABLE_NAME = UPPER('&Table_Name.')
AND Owner = UPPER('&Owner')
AND CONSTRAINT_TYPE NOT IN ( 'P' , 'R');
/
select NUM_ROWS from all_tables
where TABLE_NAME = UPPER('&Table_Name')
and Owner = UPPER('&Owner')
/
SET FEEDBACK ON
PROMPT
PROMPT Tables That REFER to Table &Table_Name.
BREAK ON TABLE_NAME ON CONSTRAINT_NAME skip 1
SELECT C.TABLE_NAME , C.CONSTRAINT_Name , CC.COLUMN_NAME "FK Column" FROM ALL_CONSTRAINTS C , All_Cons_colUMNs CC
WHERE C.Constraint_Name = CC.Constraint_Name
AND R_CONSTRAINT_NAME = ( SELECT CONSTRAINT_NAME FROM ALL_CONSTRAINTS
WHERE TABLE_NAME = UPPER('&Table_Name')
AND CONSTRAINT_TYPE = 'P'
AND Owner = UPPER('&Owner')
)
AND C.Owner = UPPER('&Owner')
/
CLEAR BREAKS
PROMPT
PROMPT PARTITIONED COLUMNS for Table &Table_Name.
SELECT COLUMN_NAME , COLUMN_POSITION FROM All_Part_Key_Columns
WHERE NAME = UPPER('&Table_Name')
AND Owner = UPPER('&Owner') ;
PROMPT
PROMPT PARTITIONS for Table &Table_Name.
-- SELECT PARTITION_NAME , NUM_ROWS
-- FROM All_Tab_Partitions
-- WHERE TABLE_NAME = UPPER('Table_Name')
-- AND Table_Owner = UPPER('Owner') ;
break on report
compute sum of NUM_ROWS on report
select partition_position, partition_name, num_rows from all_tab_partitions
where table_name = UPPER('&Table_Name')
AND Table_Owner = UPPER('&Owner') ;
prompt if table is partitioned num_rows is blank ... then run following command
prompt execute dbms_stats.gather_table_stats(UPPER('..Owner.'),UPPER('..Table_Name.'));
PROMPT
PROMPT TRIGGERS for Table &Table_Name.
SELECT Trigger_Name FROM All_Triggers
WHERE TABLE_NAME = UPPER('&Table_Name')
AND Owner = UPPER('&Owner') ;
PROMPT
PROMPT DEPENDANTS for Table &Table_Name.
BREAK ON TYPE SKIP 1
SELECT TYPE , NAME FROM ALL_DEPENDENCIES
WHERE REFERENCED_NAME = UPPER('&Table_Name')
AND Owner = UPPER('&Owner')
ORDER BY TYPE ;
CLEAR BREAKS
SET TERMOUT ON
UNDEF Owner
UNDEF Table_Name
prompt --------CUT-----------------------------CUT--------------------------CUT-----------------------------CUT---------------------