Find Unindexed Foreign Keys (2005)

--  ----------------------------------------------------------------------
--  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;