CODEWITHSUNDEEP
sqloracle11g
Narasimha Maiya
Narasimha Maiya

Reputation: 1029

Listing all the relations between views and tables

I need the list of all relationship maintained in schema.

I derived following query but it can display only table to table relationship and I don't know from where should I get table to view or view to view relationship Information from 

SELECT  main.table_name  parent, par_col.COLUMN_NAME par_column,
    link.table_name child,link_col.COLUMN_NAME child_column
FROM    user_constraints main, user_constraints link,
user_cons_columns par_col, user_cons_columns link_col
WHERE   main.constraint_type    IN ('P', 'U')
AND link.r_constraint_name  = main.constraint_name
and main.constraint_name = par_col.CONSTRAINT_NAME
and link.r_constraint_name = link_col.CONSTRAINT_NAME
and main.owner = '<schema_name>'
ORDER BY  main.table_name, link.table_name;

Thank you

Upvotes: 0

Views: 49

Answers (1)

Suleymani
Suleymani

Reputation: 161

This will help your ( in T-SQL) :

SELECT
    FrK.name 'FK Name',
    tp.name 'Main_Parent table',
    cp.name, cp.column_id,
    tr.name 'Referenced to',
    cr.name, cr.column_id
FROM 
    sys.foreign_keys FrK
INNER JOIN 
    sys.tables tp ON FrK.parent_object_id = tp.object_id
INNER JOIN 
    sys.tables tr ON FrK.referenced_object_id = tr.object_id
INNER JOIN 
    sys.foreign_key_columns fkc ON fkc.constraint_object_id = FrK.object_id
INNER JOIN 
    sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id
INNER JOIN 
    sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id

For Oracle check this one:

select table_name 
from all_constraints 
where constraint_type='R' 
  and r_constraint_name in (select constraint_name   
                            from all_constraints
                            where constraint_type in ('P','U')   
                              and table_name='<your table here>');

Upvotes: 1

Related Questions