Re: Selecting list of foreign keys enabled on a table

From: John Strange <jstrange_at_imtn.dsccc.com>
Date: 5 Aug 1998 11:54:09 GMT
Message-ID: <6q9h51$sku$1@relay1.dsccc.com>

here try this

/*********************************************************************
*
*	desc_constr.sql - Describe all Relational constrants on a table
*
*	output 80 col - desc_constr.lis

*

*********************************************************************/

set pause off
set verify off
set pagesize 199
set linesize 80

column constraint_name format a25
column table_name format a25
column column_name format a53  

spool desc_constr.lis
select acc.constraint_name || chr(9) constraint_name,

        rtrim (acc.table_name) || '.' || rtrim (acc.column_name) column_name

from all_cons_columns acc,
     all_constraints ac,
     all_constraints acr

where ac.constraint_name = acc.constraint_name   and acr.constraint_name = ac.r_constraint_name   and acr.table_name = upper ('&table_name')   and ac.constraint_type = 'R'
order by acc.constraint_name, position
/
spool off
exit ;

/**********************end desc_constr.sql ***********************************************/

Alexander Bisset (a-bisset_at_aberdeen-harbour.co.uk) wrote:
: I wish to truncate a table but I am getting a "ORA-02266: unique/primary
: keys in table referenced by enabled foreign keys" message.

: I understand why this is happening what I would like to know is how I can
: get a list of all foreign keys that are referencing the table I want to
: drop. I thought I had disabled all of them but there must be one eluding me.

: Thanks in advance,

--
While DSC may claim ownership of all my ideas (on or off the job), DSC does not claim any responsibility for them. Warranty expired when you opened this article and I will not be responsible for its contents or use. Received on Wed Aug 05 1998 - 06:54:09 CDT