CREATE BLOG dbo.SqlServer: Function that Retrieves All Foreign Key Columns that are not in an Index

Thursday, August 24, 2006

Function that Retrieves All Foreign Key Columns that are not in an Index

I was doing some performance-related data collection for a client today and thought I'd share one thing I wrote - a very cool script.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[DBA_GetForeignKeyColumnsNotInAnyIndex] ()
RETURNS TABLE
AS
/*
Author: Scott Whigham from http://www.LearnSqlServer.com/

Description: This is a helpful script to run on a new database or even periodically. Since JOIN
candidates can usually benefit from an index, this query identifies any ChildTable.ForeignKeyColumns
that are not in any index whatsoever. It does not matter to this script whether or not the column
is the leading, middle, or last column in the index. You could easily modify this to find foreign key columns
that were not the leading column in an index, find those not in a clustered index, et al.
Feel free to modify this at will!

Misc Notes: I like this in the "model" database so that it will be propagated to any new databases on the server.
We can't just dump it in "master" since INFORMATION_SCHEMA views do not change context.
I wrote this as a function instead of a view because I may wish to add parameters to it later on.
I prefer this to be a function instead of a proc so that I can write scripts like this:

SELECT 'CREATE INDEX [nci_' + REPLACE(REPLACE(REPLACE(ChildTable, '[', ''), ']', ''), 'dbo.', '') + '_' + Column_Name + '] ON ' + ChildTable + ' ([' + Column_Name + '])'
, 'DROP INDEX ' + ChildTable + '.[nci_' + REPLACE(REPLACE(ChildTable, '[', ''), ']', '') + '_' + Column_Name + ']'
FROM DBA_GetForeignKeyColumnsNotInAnyIndex()

Versions: SQL Server 2005+

Executing this function:
SELECT * FROM dbo.DBA_GetForeignKeyColumnsNotInAnyIndex ()
ORDER BY ChildTable, COLUMN_NAME

Creation Date: August 24, 2006

For more scripts like this one, visit http://www.LearnSqlServer.com/
*/
RETURN (
-- Foreign Key Columns
SELECT OBJECT_ID ( tc.TABLE_SCHEMA + '.' + tc.TABLE_NAME ) AS ObjectId
, '[' + tc.TABLE_SCHEMA + '].[' + tc.TABLE_NAME + ']' AS ChildTable, cu.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc -- Returns child table info
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu
ON tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'

EXCEPT

-- Columns in Indexes
SELECT DISTINCT c.object_id
, '[' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + ']' AS TableIdentifier
, c.name AS ForeignKeyColumnsNotInAnyIndex
FROM sys.all_columns c JOIN sys.index_columns sc
ON sc.object_id = c.object_id
AND sc.column_id = c.column_id
JOIN INFORMATION_SCHEMA.TABLES t
ON sc.object_id = OBJECT_ID ( t.TABLE_SCHEMA + '.' + t.TABLE_NAME )
)
GO
SELECT * FROM [DBA_GetForeignKeyColumnsNotInAnyIndex]()