Oracle table full information

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---------------------