Get all referencing tables of a table in Sql Server

Today I want to talk about how to get all referencing tables of a Sql Server table.

It is useful, for example, if you need to delete a certain record but you can’t because is referenced by many other tables.

In short we will see how to accomplish that…

First of all we need a query to get a recordset with all the referencing tables.

We can get the information we need accessing system catalog views provided by Sql Server.

The query is as follow:

SELECT t.Name AS REFERENCING_TABLE, 
       c.name AS REFERENCING_TABLE_FIELD
FROM sys.foreign_key_columns fk
INNER JOIN sys.tables as t on fk.parent_object_id = t.object_id
INNER JOIN sys.tables as rt on fk.referenced_object_id = rt.object_id
INNER JOIN sys.columns as c on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_id
WHERE rt.name = 'PARENT_TABLE' -- <== THIS IS THE REFERENCED TABLE, REPLACE WITH YOUR TABLE

The query above returns all the referencing tables of the table “PARENT_TABLE”.

Now suppose you get the following result:

REFERENCING_TABLE      REFERENCING_TABLE_FIELD
---------------------- -----------------------
CHILD_TABLE_1            ID1
CHILD_TABLE_2            ID2
CHILD_TABLE_3            ID3

and in table PARENT_TABLE you have a table with ID=3 that you want to delete.

You could use this script to generate the delete statements for the child tables:

SELECT 'DELETE FROM ' + t.Name + ' WHERE ' + c.name + ' = 3' AS DELETE_STATEMENT
FROM sys.foreign_key_columns fk
INNER JOIN sys.tables as t on fk.parent_object_id = t.object_id
INNER JOIN sys.tables as rt on fk.referenced_object_id = rt.object_id
INNER JOIN sys.columns as c on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_id
WHERE rt.name = 'PARENT_TABLE' -- <== THIS IS THE REFERENCED TABLE, REPLACE WITH YOUR TABLE

And here you can see the result:

DELETE_STATEMENT
---------------------------------------
DELETE FROM CHILD_TABLE_1 WHERE ID1 = 3
DELETE FROM CHILD_TABLE_2 WHERE ID2 = 3
DELETE FROM CHILD_TABLE_3 WHERE ID3 = 3

Now you can simply execute the delete statements to remove records in child tables, and the you can delete the record in the parent table.

See you soon!!

Related Posts