You can use SQL*Plus to provide the following details about a table:
- Column Details
- PRIMARY KEY
- INDEXES
- FOREIGN KEYS
- CONSTRAINTS
- ROWCOUNT
- Other Tables That REFER to this Table
- PARTITIONED COLUMNS
- PARTITIONS
- TRIGGERS
- 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
