REM Filename : tableinfo.sql

REM Author : Craig Richards

REM Created :

REM Version : 1.0

REM Modifications :

REM

REM Description : Displays everything assocaited with a given table

SET PAGES 1000

SET LINES 200

SET AUTOTRACE OFF

SET TIMING OFF

SET FEEDBACK ON

SET VERIFY OFF

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

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

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');

PROMPT

PROMPT ROWCOUNT for Table &Table_Name.

SET FEEDBACK OFF

SET SERVEROUTPUT ON

DECLARE N NUMBER ;

V VARCHAR2(100) ;

V := 'SELECT COUNT(*) FROM ' || UPPER('&Table_Name.') ;

EXECUTE IMMEDIATE V INTO N ;

DBMS_OUTPUT.PUT_LINE (N);

END;

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.') ;

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.')

ORDER BY TYPE ;

CLEAR BREAKS

SET TERMOUT OFF

SET AUTOTRACE ON

SET TIMING ON

SET TERMOUT ON

REM End of Script