Re: Table information script

Martin

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

Sanjay



________________________________
From: Martin Berger <martin.a.berger_at_gmail.com>
To: smishra_97_at_yahoo.com
Cc: oracle-l_at_freelists.org
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 (+)



--
http://www.freelists.org/webpage/oracle-l

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
>
>
>TIA
>Sanjay
>__________________________________________________
>Do You Yahoo!?
>Tired of spam? Yahoo! Mail has the best spam protection around 
>http://mail.yahoo.com



      

--
http://www.freelists.org/webpage/oracle-l