Tuesday, February 21, 2012

How to find a table being refferenced ?

You want to know does a TABLE referenced as foreign key in any other table ? Let execute the following code and find out the real truth.

select a.owner,
       a.table_name,
       a.constraint_name,
       a.status
    from   all_constraints a
    where  a.constraint_type = 'R'
    and exists(
              select 1
              from   all_constraints
              where  constraint_name=a.r_constraint_name
              and constraint_type in ('P', 'U')
              and table_name = :OBJECT_NAME        --------name of the table from which you are not able to deleting
              AND OWNER = :OBJECT_OWNER)      -------- name of the schema user name
              ORDER BY TABLE_NAME, CONSTRAINT_NAME
 
 
Hopes this helps.... and leave you comments 

1 comment:

  1. Well, your site is very good for the beginners as well as professionals.You have put lot of information on this site for references.I've learnt a lot from this site. Thanks.

    ReplyDelete