Technical Article

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

Rate

4.5 (8)

Share

Share

Rate

4.5 (8)