To find the column refrence

goodman2253 · #1 (**permalink**) 06-29-12, 02:45

Hi All
Is it possible to find the refrence(primary or Foreign) of a column in another tables.
Like for example i have a column name tcolumn i have to find in which tables it is present and what is the reference

Thanks,

Littlefoot · #2 (**permalink**) 06-29-12, 02:56

Query USER_CONSTRAINTS and USER_CONS_COLUMNS.

spacebar · #3 (**permalink**) 06-29-12, 02:59

I think this will show you the tables where the column is used and the tables where it is referenced:

Code:

select atc.owner,  atc.table_name, atc.column_name, atc.data_type as "type", atc.data_length as "length",
       atc.data_precision as "precision", atc.data_scale as "scale", atc.nullable, acc.comments
 from  sys.all_tab_columns atc left outer join sys.all_col_comments acc on atc.owner        =  acc.owner
                                                                       and atc.table_name   =  acc.table_name
                                                                       and atc.column_name  =  acc.column_name
where atc.column_name  =  'column_name'

select *
 from  sys.all_cons_columns
where  column_name  =  'column_name'

goodman2253 · #4 (**permalink**) 06-29-12, 03:04

But how can i find that given column name is foreign key in particular table or primary key

spacebar · #5 (**permalink**) 06-29-12, 03:12

sys.all_cons_columns and sys.all_constraints

dayneo · #6 (**permalink**) 06-29-12, 03:47

I think this is what you are after:

Code:

-- Given a column name, provide the tables in which it is a foreign key 
-- and provide the parent table it relates to to
select fk.owner fk_owner, fk.table_name child_table, fk.constraint_name fk_name, 
       pk.owner pk_owner, pk.table_name parent_table, pk.constraint_name pk_name, 
       decode(pk.constraint_type, 'P', 'Primary Key', 
                                  'U', 'Unique Key', 
                                  pk.constraint_type) pk_type
  from all_constraints  pk,
       all_constraints  fk, 
       all_cons_columns fkcol
 where fk.owner = fkcol.owner 
   and fk.constraint_name = fkcol.constraint_name
   and fk.constraint_type = 'R'
   and pk.owner = fk.r_owner
   and pk.constraint_name = fk.r_constraint_name
   and fkcol.column_name = 'TCOLUMN'

spacebar · #7 (**permalink**) 06-29-12, 19:39

This is a little mod of the sql from dayneo which I believe will also show you the 'check' constraints for that column:

Code:

select ac.owner owner, ac.table_name, ac.constraint_name,
       decode( ac.constraint_type, 'P', 'Primary Key',
                                   'U', 'Unique Key',
                                   'R', 'Foreign Key',
                                   'C', 'Check Constraint',
                                   ac.constraint_type ) as constraint_type,
       ac.r_owner, ac.r_constraint_name, ac.search_condition, ot.table_name, acc.column_name
 from  all_constraints ac join all_cons_columns acc on  ac.owner             =  acc.owner 
                                                   and  ac.constraint_name   =  acc.constraint_name
                                                   and  acc.column_name      =  'CUST_ID'
               left outer join all_constraints  ot  on  ac.r_owner           =  ot.owner
                                                   and  ac.r_constraint_name =  ot.constraint_name

Internet Services

Web Development

Digital Marketing

Consumer Tech