ForeignKeysWithoutSupportingIndexes.sql

/*
Purpose:
- Find enabled foreign keys whose child columns are not the leading columns of an index.
- Supporting indexes can reduce parent-row update/delete checks and improve common joins.

Safety:
- Read-only. The result is a review list, not a mandate to create an index.

Requirements:
- SQL Server 2017 or later for STRING_AGG.

Customization:
- Run in the database you want to inspect; no database name is hard-coded.
*/

SET NOCOUNT ON;

;WITH ForeignKeyColumnCounts AS
(
    SELECT
        fkc.constraint_object_id,
        COUNT(*) AS column_count
    FROM sys.foreign_key_columns AS fkc
    GROUP BY fkc.constraint_object_id
),
ForeignKeyDetails AS
(
    SELECT
        fk.object_id AS foreign_key_id,
        fk.name AS foreign_key_name,
        fk.parent_object_id,
        fk.referenced_object_id,
        fkc.constraint_column_id,
        fkc.parent_column_id,
        pc.name AS parent_column_name
    FROM sys.foreign_keys AS fk
    JOIN sys.foreign_key_columns AS fkc
        ON fkc.constraint_object_id = fk.object_id
    JOIN sys.columns AS pc
        ON pc.object_id = fkc.parent_object_id
       AND pc.column_id = fkc.parent_column_id
    WHERE fk.is_disabled = 0
)
SELECT
    ps.name AS schema_name,
    pt.name AS table_name,
    fk.name AS foreign_key_name,
    STRING_AGG(QUOTENAME(fkd.parent_column_name), ', ')
        WITHIN GROUP (ORDER BY fkd.constraint_column_id) AS foreign_key_columns,
    rs.name AS referenced_schema_name,
    rt.name AS referenced_table_name,
    counts.column_count
FROM sys.foreign_keys AS fk
JOIN sys.tables AS pt
    ON pt.object_id = fk.parent_object_id
JOIN sys.schemas AS ps
    ON ps.schema_id = pt.schema_id
JOIN sys.tables AS rt
    ON rt.object_id = fk.referenced_object_id
JOIN sys.schemas AS rs
    ON rs.schema_id = rt.schema_id
JOIN ForeignKeyColumnCounts AS counts
    ON counts.constraint_object_id = fk.object_id
JOIN ForeignKeyDetails AS fkd
    ON fkd.foreign_key_id = fk.object_id
WHERE fk.is_disabled = 0
  AND NOT EXISTS
  (
      SELECT 1
      FROM sys.indexes AS i
      WHERE i.object_id = fk.parent_object_id
        AND i.index_id > 0
        AND i.is_disabled = 0
        AND i.is_hypothetical = 0
        AND NOT EXISTS
        (
            SELECT 1
            FROM sys.foreign_key_columns AS expected
            LEFT JOIN sys.index_columns AS actual
                ON actual.object_id = expected.parent_object_id
               AND actual.index_id = i.index_id
               AND actual.key_ordinal = expected.constraint_column_id
               AND actual.is_included_column = 0
            WHERE expected.constraint_object_id = fk.object_id
              AND (
                  actual.column_id IS NULL
                  OR actual.column_id <> expected.parent_column_id
              )
        )
  )
GROUP BY
    ps.name,
    pt.name,
    fk.name,
    rs.name,
    rt.name,
    counts.column_count
ORDER BY ps.name, pt.name, fk.name;