RE: Problem in fetching Table Info

From: Scott Crabtree <scott_at_psscorp.com>
Date: Mon, 14 May 2001 18:32:14 -0700
Message-ID: <F001.00303278.20010514182019@fatcity.com>

Manoj,

Nothing helps your sql skills like traversing the data dictionary, put on your hip waders get out your TUSC posters, a magnifying glass, a bottle of scotch and dive in. Here is one way, by no means the best or the only.

SELECT A.column_name, A.data_type,

DECODE(A.data_type,'NUMBER',
DECODE(A.data_precision,NULL,A.data_length,A.data_precision),

     A.data_length) as Length,
     A.nullable, min(con_p) || min(con_c) ||  min(con_r), column_id
FROM 
     user_tab_columns A, (select B.constraint_name con_name,
                DECODE(B.constraint_type,'P','P',null) con_p,
                DECODE(B.constraint_type,'C','C',null) con_c,
                DECODE(B.constraint_type,'R','R',null) con_r,
                B.table_name tname, C.Column_name col_name
                FROM    user_constraints B, user_cons_columns C
                WHERE   B.constraint_name = C.constraint_name
                      AND B.Table_name = 'AG_PRODUCT'
                      ) my_all_cons

WHERE

  A.column_name= my_all_cons.col_name (+) AND
  A.table_name = my_all_cons.tname (+) AND
  A.table_name = 'AG_PRODUCT'

GROUP BY A.column_name, A.data_type,
DECODE(A.data_type,'NUMBER',
DECODE(A.data_precision,NULL,A.data_length,A.data_precision),

     A.data_length),
     A.nullable,
     column_id

ORDER BY COLUMN_ID If you like recursive SQL, take the table_name out of the inline view and join my_all_cons.tname = A.table_name in the outer. Great for watching recursive sql for those with Unabridged Data Dictionaries........

HTH, Scott Crabtree

-----Original Message-----

Sent: Monday, May 14, 2001 11:31 AM
To: Multiple recipients of list ORACLE-L

Hi Gurus,

I am facing some problem while fetching data from oracle.
I have to fetch all info like
column_name,data_type,data_length,Nullable and primary key for particular table.
For that i had prepared 2 quiries like:
select A.column_name,A.data_type,
2 decode(A.data_type,'NUMBER',
  3
decode(A.data_precision,NULL,A.data_length,A.data_precision),   4 A.data_length) as Length,
  5 A.nullable from
  6* user_tab_columns A where table_name = 'TEST'

Which gives me info as :

COLUMN_NAME                    DATA_TYPE     LENGTH N


------------------------------ --------- ---------- -

FLD1                           NUMBER             5 N
FLD2                           NUMBER             3 N
FLD3                           VARCHAR2          10 N
FLD4                           VARCHAR2          30 Y
FLD5                           VARCHAR2         100 Y
FLD6                           VARCHAR2          50 Y
FLD7                           NUMBER            22 Y

Which is fine,but along with this i want info like which fields are pkey for which i have one more query:

select C.column_name,B.constraint_type from user_constraints B,user_cons_columns C
where
B.constraint_name=C.constraint_name and
B.table_name='TEST'

which gives me info as to which are pkeys in table, But when i join these 2 quiries i get only 3 fields which are in primary key like FLD1,FLD2,FLD3

COLUMN_NAME                    DATA_TYPE     LENGTH N

C
------------------------------ --------- ---------- -
-

FLD1                           NUMBER             5 N
P
FLD2                           NUMBER             3 N
P
FLD3                           VARCHAR2          10 N

whereas i want all other fields also along with that. Output should be as :

COLUMN_NAME                    DATA_TYPE     LENGTH N


------------------------------ --------- ---------- -

FLD1                           NUMBER             5 N
P
FLD2                           NUMBER             3 N
P
FLD3                           VARCHAR2          10 N
P
FLD4                           VARCHAR2          30 Y
FLD5                           VARCHAR2         100 Y
FLD6                           VARCHAR2          50 Y
FLD7                           NUMBER            22 Y

I tried following query for this:
  1 select A.column_name,A.data_type,
  2 decode(A.data_type,'NUMBER',
  3
decode(A.data_precision,NULL,A.data_length,A.data_precision),   4 A.data_length) as Length,
  5 A.nullable,B.constraint_type from

  6   user_tab_columns A,
  7   user_constraints B,
  8   user_cons_columns C

9 where

 10   B.constraint_name=C.constraint_name and
 11   A.column_name=C.column_name(+) and
 12   A.table_name=B.table_name and
 13*  A.table_name = 'GEMS

Could any one Help me in this??

TIA
Manoj.

Do You Yahoo!?
Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Soman Manoj
INET: manojdsoman_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Scott Crabtree
INET: scott_at_psscorp.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message