mssqltips logo

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

By:   |   Updated: 2017-08-17   |   Comments (1)   |   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.

query output

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:

query output

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.


Last Updated: 2017-08-17


get scripts

next tip button



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

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





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

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


download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools