Script to identify all non-indexed foreign keys in a SQL Server database

By: Eli Leiba | Comments (3) | Related: More > Indexing

Problem

The requirement is to create a query that will identify all SQL Server foreign keys that do not have a corresponding index built on that column for that table. Having these columns indexed can help improve SQL Server performance.

Solution

The solution involves creating a query against system views and show the table name and the column name for the columns that appear in a foreign key constraint where there is not an index setup starting with the first column of the foreign key.

Here is what the query does:

The query consists of two main queries with an EXCEPT operator between the two queries.
The first query is a join between sys.foreign_key_columns, sys.all_columns and sys.objects system views.
The query lists all the foreign key columns and table names for non-Microsoft shipped objects.
The query does not list the reference column names since it is assumed that they are indexed by the primary keys of the reference tables.
The second query is a join between sys.index_columns, sys.all_columns and sys.objects system views.
The query lists all the index columns that appear as the first column in the index along with the table names.
This is done for all indexes in the database, for all the non-Microsoft shipped objects.
The EXCEPT operator gives us all the foreign key columns and table names that do NOT appear in any index in the first column of the index.

T-SQL Code

SELECT 
   Object_Name(a.parent_object_id) AS Table_Name
   ,b.NAME AS Column_Name
FROM 
   sys.foreign_key_columns a
   ,sys.all_columns b
   ,sys.objects c
WHERE 
   a.parent_column_id = b.column_id
   AND a.parent_object_id = b.object_id
   AND b.object_id = c.object_id
   AND c.is_ms_shipped = 0
EXCEPT
SELECT 
   Object_name(a.Object_id)
   ,b.NAME
FROM 
   sys.index_columns a
   ,sys.all_columns b
   ,sys.objects c
WHERE 
   a.object_id = b.object_id
   AND a.key_ordinal = 1
   AND a.column_id = b.column_id
   AND a.object_id = c.object_id
   AND c.is_ms_shipped = 0
GO

Example Use

If I run the above query against the Northwind database this is the result.

This is helpful, but to take this a step further I want to see more information about the foreign keys and the referenced table.

T-SQL Code with Additional Data

So I took the first query and made it a CTE and then added more system views to return additional information as shown below.

WITH v_NonIndexedFKColumns AS (
   SELECT 
      Object_Name(a.parent_object_id) AS Table_Name
      ,b.NAME AS Column_Name
   FROM 
      sys.foreign_key_columns a
      ,sys.all_columns b
      ,sys.objects c
   WHERE 
      a.parent_column_id = b.column_id
      AND a.parent_object_id = b.object_id
      AND b.object_id = c.object_id
      AND c.is_ms_shipped = 0
   EXCEPT
   SELECT 
      Object_name(a.Object_id)
      ,b.NAME
   FROM 
      sys.index_columns a
      ,sys.all_columns b
      ,sys.objects c
   WHERE 
      a.object_id = b.object_id
      AND a.key_ordinal = 1
      AND a.column_id = b.column_id
      AND a.object_id = c.object_id
      AND c.is_ms_shipped = 0
   )
SELECT 
   v.Table_Name AS NonIndexedCol_Table_Name
   ,v.Column_Name AS NonIndexedCol_Column_Name             
   ,fk.NAME AS Constraint_Name   
   ,SCHEMA_NAME(fk.schema_id) AS Ref_Schema_Name       
   ,object_name(fkc.referenced_object_id) AS Ref_Table_Name      
   ,c2.NAME AS Ref_Column_Name         
FROM 
   v_NonIndexedFKColumns v
   ,sys.all_columns c
   ,sys.all_columns c2
   ,sys.foreign_key_columns fkc
   ,sys.foreign_keys fk
WHERE 
   v.Table_Name = Object_Name(fkc.parent_object_id)
   AND v.Column_Name = c.NAME
   AND fkc.parent_column_id = c.column_id
   AND fkc.parent_object_id = c.object_id
   AND fkc.referenced_column_id = c2.column_id
   AND fkc.referenced_object_id = c2.object_id
   AND fk.object_id = fkc.constraint_object_id
ORDER BY 1,2

The above returns the following:

You can then use this output to create new indexes for these tables and columns.

So for example, looking at the first row returned, I could create a new index on table CustomerCustomerDemo for column CustomerTypeID.

Next Steps

Use these queries to help identify potential new indexes.
You can use these queries to create views in your application database.
The code was tested using: Microsoft SQL Server 2012, 2014 and 2016.

About the author

Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

View all my tips

Comments For This Article

Sunday, April 18, 2021 - 10:11:24 PM - Brett Shearer	Back To Top (88559)
This query currently allows non matching filtered indexes to affect results.

Monday, January 20, 2020 - 4:54:59 AM - Eitan Blumin

The following script should automatically generate CREATE INDEX commands as needed (don't forget to remove the ONLINE=ON part for non Enterprise editions):

SELECT 
    OBJECT_NAME(a.parent_object_id) AS Table_Name
   ,b.NAME AS Column_Name
   ,N'CREATE NONCLUSTERED INDEX ' + QUOTENAME(N'IX_' + b.[name]) + N' ON '
    + QUOTENAME(OBJECT_SCHEMA_NAME(a.parent_object_id)) + N'.' + QUOTENAME(OBJECT_NAME(a.parent_object_id)) + N'(' + QUOTENAME(b.[name]) + N' ASC)
WITH (ONLINE=ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
GO'
FROM 
    sys.foreign_key_columns a
   ,sys.all_columns b
   ,sys.objects c
WHERE 
   a.parent_column_id = b.column_id
   AND a.parent_object_id = b.object_id
   AND b.object_id = c.object_id
   AND c.is_ms_shipped = 0
   AND NOT EXISTS (
SELECT 
   Object_name(a1.Object_id)
FROM 
   sys.index_columns a1
WHERE 
   a1.object_id = b.object_id
   AND a1.key_ordinal = 1
   AND a1.column_id = b.column_id
   AND a1.object_id = c.object_id)

Thursday, April 18, 2019 - 2:45:26 PM - David Gillett

Change parent_object_id to referenced_object_id and this works perfectly

WITH v_NonIndexedFKColumns AS (
   SELECT 
      Object_Name(a.REFERENCED_object_id) AS Table_Name
      ,b.NAME AS Column_Name