Sunday, January 17, 2016

How to find out where reference to primary key is used in SQL Server?

I have a table in SQL Server 2008 R2 with primary key called ID which then is used by multiple tables around the database as foreign key. How to find out by which tables it is used? I'm trying to delete that record but it's complaining that ID is in use.
Or maybe there's an easy way to delete all referenced records from whole database just by giving database that ID? Right now I'm going for each table (that I know has that ID as foreign key) and deleting records that correspond to that particular ID but if there's better/simpler way to find it and delete all at once with simple code then that would best idea.

Ans)

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) = 'SchoolInvoice'

Reference:
http://sqlhints.com/2014/05/17/how-to-find-whether-a-table-is-referenced-by-the-foreign-key-constraints-defined-in-another-table-sql-server/


No comments:

Post a Comment