Saturday, September 9, 2017

Query to Identify Primary Table is being used as Reference in which Tables in the SQL Database

Just write down the PrimaryTableName
and execute below query.

SELECT OBJECT_NAME (FK.referenced_object_id) 'Referenced Table',
OBJECT_NAME(FK.parent_object_id) 'Referring Table',
FK.name 'Foreign Key',
COL_NAME(FK.referenced_object_id,
FKC.referenced_column_id) 'Referenced Column',
 COL_NAME(FK.parent_object_id,FKC.parent_column_id) 'Referring Column'
FROM sys.foreign_keys AS FK
INNER JOIN sys.foreign_key_columns AS FKC
ON FKC.constraint_object_id = FK.OBJECT_ID
WHERE OBJECT_NAME (FK.referenced_object_id) = 'PrimaryTableName'

If you want to check multiple Primary Table then just replace
OBJECT_NAME (FK.referenced_object_id) = 'PrimaryTableName' by
OBJECT_NAME (FK.referenced_object_id) in
('PrimaryTable1','PrimaryTable2','PrimaryTable2')

No comments:

Post a Comment