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

By: Eli Leiba   |   Updated: 2017-08-17   |   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