Re: Table information script

From: Sanjay Mishra <>
Date: Fri, 14 Aug 2009 08:02:07 -0700 (PDT)
Message-ID: <>


Perfectly this will provide me the required information. Thanks for quick update. I will utilize to automate it for all schema and their tables.


From: Martin Berger <>
Sent: Friday, August 14, 2009 12:47:06 AM
Subject: Re: Table information script

does this answer your question?

-- Owner   TableName   ColumnName   DataType    Null/NotNull        PrimaryKey(Y/N)   Foreign Key(Y/N)   ParentTable

select tab.owner Owner,  
       tab.table_name TableName, 
       tab.column_name ColumnName, 
       tab.data_type   DataType, 
       tab.nullable    "Null",
       decode(con.constraint_type, 'P', 'Y', 'N') PrimaryKey,
       decode(con.constraint_type, 'R', 'Y', 'N') ForeignKey,
       conf.table_name ParentTable
from dba_cons_columns col, dba_constraints con, 
     dba_tab_columns tab, dba_constraints conf
where tab.owner = col.owner
  and tab.table_name = col.table_name
  and tab.column_name = col.column_name
  and col.owner = con.owner
  and col.constraint_name = con.constraint_name
  and con.r_owner = conf.owner (+)
  and con.r_constraint_name = conf.constraint_name (+)


Am 14.08.2009 um 03:57 schrieb Sanjay Mishra:

Can somebody share the query that can give the table information with Primary and Foregin Key as shown in tools like Toad or as shown below
>Owner   TableName   ColumnName   DataType    Null/NotNull        PrimaryKey(Y/N)   Foreign Key(Y/N)   ParentTable
>Do You Yahoo!?
>Tired of spam? Yahoo! Mail has the best spam protection around 

Received on Fri Aug 14 2009 - 10:02:07 CDT

Original text of this message