RE: Create info about table definition

From: Bill Threlkel <bill_at_cybertrails.com>
Date: Mon, 25 Mar 2002 19:18:20 -0800
Message-ID: <F001.00432BCD.20020325191820@fatcity.com>

try running these scripts, too. I use them when building queries.

--script 1
TTITLE CENTER 'TABLE RELATIONSHIPS' SKIP 1 -

CENTER '******************************' SKIP 1 -

'This report lists all tables with foreign keys that reference' SKIP 1 -
'the "table_name" searched for.' SKIP 1 -

CENTER '******************************' SKIP 1 -

SET LINESIZE 100
SET PAGESIZE 25 COLUMN column_name HEADING 'Queried Col Name' FORMAT A30 COLUMN source HEADING 'Ref Col Name' FORMAT A30

SELECT allc2.table_name Ref_Table, allcc1.column_name source, allcc2.column_name
FROM all_constraints allc1, all_constraints allc2, all_cons_columns allcc1, all_cons_columns allcc2
WHERE allc1.constraint_name = allc2.r_constraint_name AND allcc2.constraint_name = allc1.constraint_name AND allc2.constraint_name = allcc1.constraint_name AND allc1.table_name = '&table_name';

--script 2
TTITLE CENTER 'TABLE RELATIONSHIPS' SKIP 1 -

CENTER '******************************' SKIP 1 -

'This report lists all tables and columns referenced by' SKIP 1 -
'the "table_name" searched for.' SKIP 1 -

CENTER '******************************' SKIP 1 -

SET LINESIZE 100
SET PAGESIZE 25 COLUMN Table_ HEADING 'Ref_Table_Name' FORMAT A30 COLUMN Ref_column_name HEADING 'Ref_column_name' FORMAT A30 COLUMN Queried_Col_Name HEADING 'Queried_Table_Col_Name' FORMAT A30

SELECT allcc1.table_name Table_, allcc1.column_name Ref_column_name, allcc2.column_name Queried_Col_Name
FROM all_constraints allc1, all_cons_columns allcc1, all_cons_columns allcc2 WHERE allc1.table_name = '&table_name'
AND allc1.r_constraint_name = allcc1.constraint_name AND allc1.constraint_name = allcc2.constraint_name;

-----Original Message-----
Roland.Skoldblom_at_ica.se
Sent: Monday, March 25, 2002 3:53 AM
To: Multiple recipients of list ORACLE-L

Hallo,

is there any way to create a table of the definition of table( list of field names and their datatypes, indexes etc)

Thanks in advance

Roland S

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: Roland.Skoldblom_at_ica.se

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: Bill Threlkel
  INET: bill_at_cybertrails.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).

Received on Mon Mar 25 2002 - 21:18:20 CST