How to identify non-indexed foreign keys in SQL Server

Here is a script to identify foreign keys without any supporting index in SQL Server.

The script is developed on an assumption to verify ONLY the leading column of the index and the foreign key. This is to avoid any duplicate indexes that could create without a detailed analysis.


;WITH CTE AS 
(
	SELECT A.NAME,SCHEMA_NAME(A.SCHEMA_ID) SCHEMA_NAME,OBJECT_NAME(B.REFERENCED_OBJECT_ID) PARENT_TABLE_NAME,OBJECT_NAME(B.PARENT_OBJECT_ID) CHILD_TABLE_NAME,
	B.PARENT_OBJECT_ID,B.PARENT_COLUMN_ID,C.COLUMN_NAME COLUMNNAME
	,ROW_NUMBER()OVER(PARTITION BY A.NAME,A.SCHEMA_ID,B.REFERENCED_OBJECT_ID,B.PARENT_OBJECT_ID ORDER BY C.ORDINAL_POSITION ASC) RN
	FROM SYS.FOREIGN_KEYS A
	INNER JOIN SYS.FOREIGN_KEY_COLUMNS B ON A.OBJECT_ID = B.CONSTRAINT_OBJECT_ID
	INNER JOIN INFORMATION_SCHEMA.COLUMNS C ON C.COLUMN_NAME=COL_NAME(B.PARENT_OBJECT_ID,B.PARENT_COLUMN_ID) AND OBJECT_NAME(B.PARENT_OBJECT_ID)=C.TABLE_NAME 
)
SELECT A.NAME FOREIGNKEY_NAME,A.SCHEMA_NAME,A.PARENT_TABLE_NAME,A.CHILD_TABLE_NAME,A.COLUMNNAME FORIEGNKEY_LEADCOLUMN
FROM CTE A
LEFT JOIN SYS.INDEX_COLUMNS D ON D.OBJECT_ID = A.PARENT_OBJECT_ID AND D.COLUMN_ID = A.PARENT_COLUMN_ID AND KEY_ORDINAL=1 
LEFT JOIN SYS.INDEXES D1 ON D.OBJECT_ID = D1.OBJECT_ID AND D.INDEX_ID=D1.INDEX_ID
LEFT JOIN SYS.COLUMNS E ON E.COLUMN_ID=D.COLUMN_ID AND E.OBJECT_ID=D.OBJECT_ID  
WHERE D.COLUMN_ID IS NULL AND A.RN=1
ORDER BY CHILD_TABLE_NAME ASC
Advertisements

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