Oracle: get detail table information

person Jason Huangfolder_openCode, HOW-TO, Oracle, SQLlocal_offer, , , access_time September 24, 2009

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


The following SQL Script can 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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>