T-SQL Script for finding the Foreign Keys for a Primary Key Column in SQL Server

From the below script we can find out how many Foreign keys are there for a particular Primary key column of a specified table. Commenting the Where clause at end of the script will give list of Primary keys and their corresponding foreign keys in a database

SELECT
	RE.PK_TABLE_NAME 
      ,	RE.PK_CONSTRAINT_NAME 
      ,	C.TABLE_NAME FK_TABLE_NAME
      ,	C.COLUMN_NAME KF_COLUMN_NAME
      ,	RE.FK_CONSTRAINT_NAME
FROM
(
	SELECT
		A.TABLE_NAME PK_TABLE_NAME
                ,	A.CONSTRAINT_NAME PK_CONSTRAINT_NAME
                , B.CONSTRAINT_NAME FK_CONSTRAINT_NAME
	FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS A
	JOIN INFORMATION_SCHEMA .REFERENTIAL_CONSTRAINTS B
		ON A.CONSTRAINT_NAME = B.UNIQUE_CONSTRAINT_NAME
) RE
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE C
	ON RE.FK_CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE PK_TABLE_NAME = 'Required table name'
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: