Problem solve Get help with specific problems with your technologies, process and projects.

Get useful details about a table with one query

With this code, one can get all the details of a table just by passing the table name as a parameter to the query.

This tip tells all the following details about a table structure and dependencies at one go:

  • Comments for the table
  • Column Details, including comments for each table
  • PRIMARY KEY
  • INDEXES
  • FOREIGN KEYS
  • CONSTRAINTS
  • ROWCOUNT
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 9.2.0.1.0.

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

Reader Feedback

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.

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close