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
JOINsys.foreign_key_columns                     AS foreign_key_columns
  ONforeign_keys."object_id" = foreign_key_columns.constraint_object_id
WHERENOT EXISTS (
SELECT'An index with same columns and column order'
FROM    sys.indexes                                 AS indexes
JOINsys.index_columnsAS index_columns
  ONindexes."object_id" = index_columns."object_id"
WHEREforeign_keys.parent_object_id = indexes."object_id"
  ANDindexes.index_id = index_columns.index_id
  ANDforeign_key_columns.constraint_column_id = index_columns.key_ordinal
  ANDforeign_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;