Getting Table Details with SQL*Plus

You can use SQL*Plus to provide the following details about a table:

1. Column Details
2. PRIMARY KEY
3. INDEXES
4. FOREIGN KEYS
5. CONSTRAINTS
6. ROWCOUNT
7. Other Tables That REFER to this Table
8. PARTITIONED COLUMNS
9. PARTITIONS
10. TRIGGERS
11. DEPENDANTS

Use the following code in SQL*Plus to provide this information:

      SET AUTOTRACE OFF
     SET TIMING 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 CONS! TRAINT_T YPE = '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 NU MBER ;
     V VARCHAR2(100) ;
     BEGIN
     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