Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2012
    Posts
    37

    Unanswered: To find the column refrence

    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,

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Query USER_CONSTRAINTS and USER_CONS_COLUMNS.

  3. #3
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    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'

  4. #4
    Join Date
    Mar 2012
    Posts
    37
    But how can i find that given column name is foreign key in particular table or primary key

  5. #5
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    sys.all_cons_columns and sys.all_constraints

  6. #6
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Question

    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'

  7. #7
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •