Re: Table information script

From: Martin Berger <martin.a.berger_at_gmail.com>
Date: Fri, 14 Aug 2009 06:47:06 +0200
Message-Id: <47713508-28BF-4FEE-921B-F790102F74B6_at_gmail.com>

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

Received on Thu Aug 13 2009 - 23:47:06 CDT