Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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

Learn more about SQL Server tools