Finding relationships among all the tables in a Database.

vishalneeraj-24503
Posted by vishalneeraj-24503 under Sql Server category on | Points: 40 | Views : 328
Write below query:-
SELECT
fk.name as 'Foreign Key Name',
tp.name as 'Base Tables',
cp.name as 'Base Table Column Names',
cp.column_id as 'Base Table Column Index Positions',
tr.name as 'Referred Tables',
cr.name as 'Referred Table Column Names',
cr.column_id as 'Referred Table Column Index Positions'
FROM
sys.foreign_keys fk
INNER JOIN
sys.tables tp ON fk.parent_object_id = tp.object_id
INNER JOIN
sys.tables tr ON fk.referenced_object_id = tr.object_id
INNER JOIN
sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.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
ORDER BY
tp.name,cp.column_id;

Comments or Responses

Login to post response