Tag Archives: Table Columns referenced by other tables Foreign Key Constraint

How to find whether a Table is referenced by the Foreign Key constraint defined in another Table – sql server

We can use script like below to identify whether a Table is referenced by another Tables foreign key constraints in Sql Server:

ALSO READ: How to find all dependencies of a table in Sql Server?

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) = 'Enter Table Name'

ALSO READ: How to find referenced/dependent objects (like Table, Function etc) of a Stored Procedure/Function in Sql Server?

Let us understand this with an example:

Below script creates a database SqlHintsDemo and adds to tables Customer and Order to it. Here foreign key constraints FK_Order_Customer defined on the Order Table CustID column refers to the CustomerId column of the Customer table.

CREATE DATABASE SqlHintsDemo
GO
USE SqlHintsDemo
GO
CREATE TABLE [dbo].[Customer] (
[CustomerId] [int] IDENTITY (1, 1) NOT NULL 
 PRIMARY KEY  CLUSTERED) 
GO
CREATE TABLE [dbo].[Order] (
	[OrderID] [int] IDENTITY (1, 1) NOT NULL 
         PRIMARY KEY  CLUSTERED ,
	[CustID] [int] NOT NULL ,	
	CONSTRAINT [FK_Order_Customer] FOREIGN KEY 
	([CustID]) REFERENCES [dbo].[Customer] ([CustomerId]) 
) 
GO

We can use a script like below to identify whether the Customer Table is referred by any other Tables foreign key constraint:

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

RESULT:
Table Referenced by Another Tables Foregin Key Constraint