Find Unindexed Foreign Keys (2005)

,

This script will find foreign keys (on referencing table) that are not indexed.  It looks for exact definition of index matching columns and order.

User should decide if index intersection from other indexes are sufficient, e.g., individual indexes on each column of a foreign key.

--  ----------------------------------------------------------------------
--  Author:  Michael Smith, Minneapolis, MN
--  Purpose: Find unindexed foreign keys
--  Date:    2008-08-09
--  ----------------------------------------------------------------------

--USE tempdb;

SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;


SELECT  DISTINCT      /* remove dups caused by composite constraints */
        DB_NAME()                                   AS "database_name",
        OBJECT_NAME(foreign_keys.parent_object_id)  AS "table_name",
        foreign_keys."name"                         AS "fk_name"
FROM    sys.foreign_keys                            AS foreign_keys
JOIN	sys.foreign_key_columns                     AS foreign_key_columns
  ON	foreign_keys."object_id" = foreign_key_columns.constraint_object_id
WHERE	NOT EXISTS (
			SELECT	'An index with same columns and column order'
			FROM    sys.indexes                                 AS indexes
			JOIN	sys.index_columns							AS index_columns
			  ON	indexes."object_id" = index_columns."object_id"
			WHERE	foreign_keys.parent_object_id = indexes."object_id"
			  AND	indexes.index_id = index_columns.index_id
			  AND	foreign_key_columns.constraint_column_id = index_columns.key_ordinal
			  AND	foreign_key_columns.parent_column_id = index_columns.column_id
			  AND   OBJECTPROPERTYEX(indexes."object_id",'IsMSShipped') = 0
			  AND   indexes.is_hypothetical = 0
		)

 AND    foreign_keys.is_ms_shipped = 0;

Rate

4.5 (8)

Share

Share

Rate

4.5 (8)