This tip tells all the following details about a table structure and dependencies at one go:
Generally, one has to search in a number of system tables to sort out all these details, which is time- and effort-consuming. However, with the below code, one can get all the details just by passing the table name as a parameter to the query. It has been tested on Oracle9i Enterprise Edition Release 22.214.171.124.0.
- Comments for the table
- Column Details, including comments for each table
- PRIMARY KEY
- FOREIGN KEYS
COLUMN COMMENTS FORMAT A50 COLUMN column_name FORMAT A50 COLUMN Data_Type FORMAT A20 UNDEF Table_Name PROMPT Enter Table Name ACCEPT Table_Name SET HEADING OFF PROMPT PROMPT Comments for Table &Table_Name. SELECT COMMENTS FROM ALL_TAB_COMMENTS WHERE TABLE_NAME = UPPER('&Table_Name.') ; SET HEADING ON PROMPT PROMPT Column Details for Table &Table_Name. SELECT T.COLUMN_NAME , T.Data_Type , T.DATA_LENGTH, T.Nullable , 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.') / PROMPT PROMPT PRIMARY KEY for Table &Table_Name. select COLUMN_NAME FROM ALL_CONS_COLUMNS WHERE TABLE_NAME = UPPER('&Table_Name.') AND CONSTRAINT_NAME = ( SELECT CONSTRAINT_NAME FROM ALL_CONSTRAINTS WHERE TABLE_NAME = UPPER('&Table_Name.') AND CONSTRAINT_TYPE = 'P' ) 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 NOT EXISTS ( SELECT 'X' FROM ALL_CONSTRAINTS WHERE CONSTRAINT_NAME = I.INDEX_NAME ) ORDER BY INDEX_NAME , COLUMN_POSITION / PROMPT PROMPT FOREIGN KEYS for Table &Table_Name. SELECT CONSTRAINT_NAME , I.TABLE_NAME , R_CONSTRAINT_NAME FROM ALL_CONSTRAINTS C , ALL_INDEXES I WHERE C.R_CONSTRAINT_NAME = I.INDEX_NAME AND C.TABLE_NAME = UPPER('&Table_Name.') AND C.CONSTRAINT_TYPE = 'R' ORDER BY CONSTRAINT_NAME / PROMPT PROMPT CONSTRAINTS for Table &Table_Name. SELECT CONSTRAINT_NAME , SEARCH_CONDITION FROM ALL_CONSTRAINTS WHERE TABLE_NAME = UPPER('&Table_Name.') AND CONSTRAINT_TYPE NOT IN ( 'P' , 'R'); PROMPT PROMPT ROWCOUNT for Table &Table_Name. SET SERVEROUTPUT ON DECLARE N NUMBER(5) ; V VARCHAR2(100) ; BEGIN V := 'SELECT COUNT(*) FROM ' || UPPER('&Table_Name.') ; EXECUTE IMMEDIATE V INTO N ; DBMS_OUTPUT.PUT_LINE (N); END; /
Jim A. writes: A useful script if you don't have a tool like SQL*Navigator. Note: Does not work fully with versioned tables (Workspace Manager). The primary key and indexes portions require editing to remove the line feeds in the subqueries.
Rdocena writes: Great tip; kudos to Ajay Garg! To get it to work for 8i, I changed ALL_TAB_COLS to ALL_TAB_COLUMNS. In our current setup, we have users who own the same set of tables on the same instance; e.g., user - usr_devt owns table_1 while user usr_test also owns his own table-1, so I had to edit the section that lists primary key to:
select OWNER, TABLE_NAME, COLUMN_NAME FROM ALL_CONS_COLUMNS WHERE TABLE_NAME = UPPER('&&Table_Name') AND CONSTRAINT_NAME = ( SELECT CONSTRAINT_NAME FROM ALL_CONSTRAINTS WHERE TABLE_NAME = UPPER('&&Table_Name') AND CONSTRAINT_TYPE = 'P' AND OWNER = USER ) ORDER BY POSITION /
I displayed the fields OWNER and TABLE_NAME in the main query and added the condition 'AND OWNER = USER' in the subquery as without it the error 'ORA-01427: single-row subquery returns more than one row' appears.
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free Oracle tips and scripts.
- Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our applications, SQL, database administration, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
- Best Web Links: Oracle tips, tutorials, and scripts from around the Web.