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
I think this will show you the tables where the column is used and the tables where it is referenced:
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'
where column_name = 'column_name'
-- 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',
from all_constraints pk,
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'