Hi all,
i have a requirement to get the table name by using column name. i tried using below query and i get all the tables which consists of the column however i want only one table having this as a main column/base table
select a.*
from all_tab_columns a, all_objects b
where a.column_name ='MRR_ID'
and b.object_name = a.table_name
and b.object_type ='TABLE'
and a.owner ='M_SYS'
order by a.column_id
and i tried using a primary key constant on a table this is working as expected when column holds a primary key only, other cases it was not working, please find the query below
select *
from all_cons_columns ucc, all_constraints uc
where uc.constraint_name=ucc.constraint_name
and uc.constraint_type='P'
and ucc.column_name ='MRR_ID'
and uc.table_name in (select a.table_name
from all_tab_columns a, all_objects b
where a.column_name ='MRR_ID'
and b.object_name = a.table_name
and b.object_type ='TABLE'
and a.owner ='M_SYS')
any ideas on this?
Regards,
Sai Sandeep.