RE: Script to get ddl from constraints (and its dependants ) including foreign keys to recreate

From: Hollis, Les <Les.Hollis_at_ps.net>
Date: Fri, 17 Dec 2004 10:16:22 -0600
Message-ID: <FCC960FDB92F5E469A02464FF72872F4032A899C@pscdalpexch50.perotsystems.net>

SELECT constraint_name, constraint_type, deferrable, deferred, validated
FROM dba_constraints
WHERE owner=3D'HR'
AND table_name=3D'EMPLOYEES';

Change schema and table_name to suit.....

To find foreign keys on HR's EMPLOYEE table and the parent constraints, use the following query:
SELECT c.constraint_name AS "Foreign Key",=20

 p.constraint_name AS "Referenced Key",=20
 p.constraint_type,=20
 p.owner,
 p.table_name

 FROM dba_constraints c, dba_constraints p  WHERE c.owner=3D'HR'

 AND c.table_name=3D'EMPLOYEE'
 AND c.constraint_type=3D'R'
 AND c.r_owner=3Dp.owner
 AND c.r_constraint_name =3D p.constraint_name;

 Again change the schema and table name as required

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

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Juan Carlos Reyes Pacheco
Sent: Friday, December 17, 2004 9:37 AM
To: oracle-l_at_freelists.org
Subject: Script to get ddl from constraints (and its dependants ) including foreign keys to recreate

 Hi, I had been RTFM some hours, and I can find a script  to change a constraint
this is get the constraint ddl and all the constraints using that constraint

Using dbms_metadata I couldn't get foreign keys ddl,=20 I don't know if some one could help.

Thanks.
=20
Juan Carlos Reyes Pacheco
OCP
--

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

http://www.freelists.org/webpage/oracle-l Received on Fri Dec 17 2004 - 10:10:20 CST