SQL Server Foreign Keys Without Indexes Script

calendar Apr 18, 2026 / · 6 min read · sql server indexes foreign keys performance sql scripts database structure sys.foreign_keys query optimization  ·
Share on: linkedin copy

Foreign keys enforce referential integrity, but SQL Server does not automatically create indexes on the referencing (child) columns. An unindexed foreign key column forces a full table scan every time a join or a DELETE on the parent table triggers a referential check. This script identifies all foreign key columns in the current database that have no supporting index, and generates the CREATE INDEX statements to fix them.

Purpose and Overview

When you run a DELETE on a parent table, SQL Server must verify that no child rows reference the deleted parent. If the child table's foreign key column has no index, SQL Server performs a full table scan on the child table for every deleted parent row. On large tables this can make simple deletes extremely slow and cause blocking on the child table.

The same problem appears during joins: a query joining the parent and child table on the foreign key relationship must scan the child table rather than seek, increasing IO and reducing parallelism efficiency.

This post provides two scripts:

  1. Find all foreign key columns in the database with no supporting index
  2. Generate CREATE INDEX statements for each unindexed foreign key

Code Breakdown

Script 1: Find Unindexed Foreign Key Columns

 1SELECT
 2    SCHEMA_NAME(t.schema_id)                        AS schema_name,
 3    OBJECT_NAME(fk.parent_object_id)                AS table_name,
 4    fk.name                                         AS foreign_key_name,
 5    COL_NAME(fkc.parent_object_id,
 6             fkc.parent_column_id)                  AS fk_column,
 7    OBJECT_NAME(fk.referenced_object_id)            AS referenced_table,
 8    COL_NAME(fkc.referenced_object_id,
 9             fkc.referenced_column_id)              AS referenced_column,
10    fk.is_disabled
11FROM sys.foreign_keys AS fk
12INNER JOIN sys.foreign_key_columns AS fkc
13    ON  fkc.constraint_object_id = fk.object_id
14INNER JOIN sys.tables AS t
15    ON  t.object_id = fk.parent_object_id
16WHERE t.is_ms_shipped = 0
17    AND NOT EXISTS (
18        SELECT 1
19        FROM sys.index_columns AS ic
20        WHERE ic.object_id        = fkc.parent_object_id
21          AND ic.column_id        = fkc.parent_column_id
22          AND ic.index_column_id  = 1   -- must be the leading column of the index
23    )
24ORDER BY schema_name, table_name, fk_column;

The subquery in the NOT EXISTS clause checks sys.index_columns for an index where the foreign key column appears as the first column (index_column_id = 1). An index that has the FK column in position 2 or later cannot be used for a seek on that column alone, so it provides limited benefit for referential checks and join lookups.

t.is_ms_shipped = 0 restricts output to user tables and excludes system objects. fk.is_disabled is included in the output because disabled foreign keys still appear in the catalog — you may want to exclude them depending on your environment.

Script 2: Generate CREATE INDEX Statements for Unindexed FKs

 1SELECT
 2    SCHEMA_NAME(t.schema_id)                        AS schema_name,
 3    OBJECT_NAME(fk.parent_object_id)                AS table_name,
 4    fk.name                                         AS foreign_key_name,
 5    COL_NAME(fkc.parent_object_id,
 6             fkc.parent_column_id)                  AS fk_column,
 7    'CREATE INDEX IX_' +
 8        OBJECT_NAME(fk.parent_object_id) + '_' +
 9        COL_NAME(fkc.parent_object_id, fkc.parent_column_id) +
10        ' ON ' +
11        QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' +
12        QUOTENAME(OBJECT_NAME(fk.parent_object_id)) + ' (' +
13        QUOTENAME(COL_NAME(fkc.parent_object_id, fkc.parent_column_id)) +
14        ') WITH (ONLINE = ON);'                     AS create_index_statement
15FROM sys.foreign_keys AS fk
16INNER JOIN sys.foreign_key_columns AS fkc
17    ON  fkc.constraint_object_id = fk.object_id
18INNER JOIN sys.tables AS t
19    ON  t.object_id = fk.parent_object_id
20WHERE t.is_ms_shipped = 0
21    AND fk.is_disabled = 0
22    AND NOT EXISTS (
23        SELECT 1
24        FROM sys.index_columns AS ic
25        WHERE ic.object_id        = fkc.parent_object_id
26          AND ic.column_id        = fkc.parent_column_id
27          AND ic.index_column_id  = 1
28    )
29ORDER BY schema_name, table_name, fk_column;

This script produces ready-to-run CREATE INDEX statements using QUOTENAME to properly escape schema and table names that contain spaces or reserved words. WITH (ONLINE = ON) allows the index to be built without holding a schema modification lock on the table, so it can run during business hours on SQL Server Enterprise edition. On Standard edition, remove ONLINE = ON or plan for a brief lock during the build.

The generated index name follows the convention IX_TableName_ColumnName. Review and rename as appropriate before running, particularly if the table already has other indexes with similar names.

Key Benefits and Use Cases

  • Identifies foreign key columns that are causing full table scans during JOIN operations
  • Eliminates table scan bottlenecks on DELETE statements that trigger referential checks
  • Generates CREATE INDEX scripts ready to review and execute with minimal editing
  • Useful during schema audits, performance reviews, and after database migrations
  • Covers composite foreign keys — run the query per column to check each column in a multi-column FK

Performance Considerations

  • Composite foreign keys: this script checks each FK column individually. A composite FK with columns (A, B) needs an index with A as the leading column at minimum. The NOT EXISTS check on index_column_id = 1 is correct for single-column FKs, but you may need to verify multi-column FKs manually.
  • Index cost vs. benefit: adding indexes increases write overhead on INSERT, UPDATE, and DELETE operations. On tables that are insert-heavy and rarely joined, an index on the FK column may not be worth the write cost. Review the query workload before creating every generated index.
  • ONLINE = ON availability: online index builds require SQL Server Enterprise or Developer edition. On Standard edition, the index build acquires a schema modification lock for its duration.
  • Disabled foreign keys: disabled FKs (is_disabled = 1) are excluded from the index generation script in Script 2. A disabled FK provides no referential check and the missing index may not matter until the FK is re-enabled.

Practical Tips

After generating the CREATE INDEX statements, review them for duplicates. If a table has three unindexed FKs on the same column (unusual but possible after a refactor), the script will generate three identical index statements. Deduplicate before executing.

To check for composite foreign key coverage specifically, extend the query to count the number of columns per FK using sys.foreign_key_columns grouped by constraint_object_id, and flag any multi-column FK where the combined column set is not covered by a composite index in the same column order.

Schedule this script as part of a quarterly schema audit job alongside the missing indexes report from sys.dm_db_missing_index_details. Both queries identify structural gaps that accumulate over time as schemas evolve.

Conclusion

Unindexed foreign keys are a common and easy-to-fix source of slow deletes and join table scans in SQL Server databases. This script surfaces them in a single query and generates the corrective index statements, making it straightforward to include in a regular schema audit or a post-migration review.

References

Posts in this series