SQL Server: Find Tables Without Primary Keys

calendar Apr 16, 2026 / · 7 min read · sql server primary keys database structure heap tables sql scripts schema audit performance sys.tables  ·
Share on: linkedin copy

Tables without primary keys are heap structures with no guaranteed row ordering and no unique row identifier. They cause full table scans in queries that could use seeks, slow down DELETE and UPDATE operations, and are incompatible with SQL Server replication and Change Data Capture. This script finds every user table in the current database that has no primary key defined.

Purpose and Overview

A primary key enforces uniqueness on a column or set of columns and, by default, creates a clustered index that physically orders the table rows. Without a primary key, SQL Server stores rows in a heap: an unordered structure where updates and deletes must first locate rows through a full scan or a nonclustered index lookup followed by a separate heap RID lookup.

Tables without primary keys commonly appear after bulk data loads, ETL staging tables that were never cleaned up, and database migrations that moved table definitions without constraints. Over time they become a source of query performance problems and prevent enabling features that require a unique row identifier.

This post covers two scripts:

  1. Find all user tables with no primary key, including row count
  2. Generate ALTER TABLE ADD PRIMARY KEY statements for review

Code Breakdown

Script 1: Find Tables Without a Primary Key

 1SELECT
 2    SCHEMA_NAME(t.schema_id)                AS schema_name,
 3    t.name                                  AS table_name,
 4    t.create_date,
 5    SUM(p.rows)                             AS row_count,
 6    CASE
 7        WHEN SUM(p.rows) = 0     THEN 'Empty'
 8        WHEN SUM(p.rows) < 1000  THEN 'Small (< 1K rows)'
 9        WHEN SUM(p.rows) < 100000 THEN 'Medium (< 100K rows)'
10        ELSE 'Large (100K+ rows)'
11    END                                     AS size_category
12FROM sys.tables AS t
13LEFT JOIN sys.key_constraints AS kc
14    ON  kc.parent_object_id = t.object_id
15    AND kc.type = 'PK'
16LEFT JOIN sys.partitions AS p
17    ON  p.object_id = t.object_id
18    AND p.index_id IN (0, 1)   -- 0 = heap, 1 = clustered index
19WHERE t.is_ms_shipped = 0
20    AND kc.object_id IS NULL   -- no primary key found
21GROUP BY t.schema_id, t.name, t.create_date
22ORDER BY SUM(p.rows) DESC, schema_name, table_name;

The LEFT JOIN to sys.key_constraints with kc.type = 'PK' finds any primary key constraint on each table. When kc.object_id IS NULL in the WHERE clause, no primary key exists. This pattern is more reliable than checking sys.indexes for is_primary_key = 1 because a clustered index is not the same thing as a primary key — a table can have a clustered index without having a declared primary key constraint.

sys.partitions provides row counts. index_id IN (0, 1) covers both heap pages (index_id = 0) and clustered index pages (index_id = 1). Using SUM(p.rows) and GROUP BY handles partitioned tables correctly by summing rows across all partitions.

The size_category column helps prioritise which tables to address first — large tables without primary keys have the most significant performance impact.

Script 2: Check for Heap Storage (No Clustered Index)

 1-- Tables without a clustered index are stored as heaps
 2-- This is a superset of tables without primary keys
 3SELECT
 4    SCHEMA_NAME(t.schema_id)    AS schema_name,
 5    t.name                      AS table_name,
 6    SUM(p.rows)                 AS row_count,
 7    CASE
 8        WHEN EXISTS (
 9            SELECT 1
10            FROM sys.key_constraints AS kc
11            WHERE kc.parent_object_id = t.object_id
12              AND kc.type = 'PK'
13        )
14        THEN 'Has PK (nonclustered)'
15        ELSE 'No PK'
16    END                         AS primary_key_status
17FROM sys.tables AS t
18INNER JOIN sys.indexes AS i
19    ON  i.object_id = t.object_id
20    AND i.type = 0              -- 0 = heap (no clustered index)
21LEFT JOIN sys.partitions AS p
22    ON  p.object_id = t.object_id
23    AND p.index_id = 0
24WHERE t.is_ms_shipped = 0
25GROUP BY t.schema_id, t.name
26ORDER BY SUM(p.rows) DESC;

A table can be a heap in two ways: no primary key at all, or a primary key that was created as a nonclustered index (using CREATE TABLE ... PRIMARY KEY NONCLUSTERED). This second script identifies all heap tables and labels whether they have a nonclustered PK or no PK at all. Both are worth reviewing, but no-PK heaps are the higher priority.

Script 3: Generate ALTER TABLE Statements to Add a Primary Key

 1-- Review output carefully before running — choose the appropriate identity column
 2SELECT
 3    SCHEMA_NAME(t.schema_id)    AS schema_name,
 4    t.name                      AS table_name,
 5    '-- Add identity column and primary key to: ' +
 6        QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' +
 7        QUOTENAME(t.name) + CHAR(13) +
 8    'ALTER TABLE ' +
 9        QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' +
10        QUOTENAME(t.name) +
11        ' ADD ID INT IDENTITY(1,1) NOT NULL;' + CHAR(13) +
12    'ALTER TABLE ' +
13        QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' +
14        QUOTENAME(t.name) +
15        ' ADD CONSTRAINT PK_' + t.name +
16        ' PRIMARY KEY CLUSTERED (ID);'     AS suggested_fix
17FROM sys.tables AS t
18LEFT JOIN sys.key_constraints AS kc
19    ON  kc.parent_object_id = t.object_id
20    AND kc.type = 'PK'
21WHERE t.is_ms_shipped = 0
22    AND kc.object_id IS NULL
23ORDER BY schema_name, table_name;

This script generates a two-step fix: add an INT IDENTITY column and then create a clustered primary key on it. This approach works when the table has no obvious natural key. The generated statements are suggestions only — review each table's data and business requirements before applying. If a natural key already exists (e.g., a unique combination of existing columns), create the primary key on those columns instead of adding a surrogate identity.

Key Benefits and Use Cases

  • Identifies structural gaps that cause heap table scans and RID lookup overhead
  • Required check before enabling SQL Server replication (all published tables need a unique row identifier)
  • Required check before enabling Change Data Capture (CDC) and Change Tracking
  • Helps prioritise schema remediation work by sorting results by row count
  • Useful as part of a post-migration audit when moving databases between instances

Performance Considerations

  • Adding a PK to a large heap is an offline operation by default: creating a clustered primary key on a large heap table rewrites the entire table in key order. On Standard edition this blocks reads and writes for the duration. On Enterprise edition, use WITH (ONLINE = ON) to minimise locking.
  • Heap vs. clustered index read patterns: heaps with nonclustered indexes can be efficient for some access patterns (bulk inserts with no reads). Do not assume every heap needs a clustered primary key — evaluate the actual query patterns first.
  • Forwarded records: heaps accumulate forwarded record pointers when rows are updated and no longer fit in their original page. This increases IO per read. sys.dm_db_index_physical_stats reports forwarded record counts, which can help quantify the performance impact of leaving a heap untouched.
  • Row size and fill factor: when converting a heap to a clustered table, choose a fill factor appropriate for the expected update rate to leave room for row growth without causing page splits.

Practical Tips

To find heap tables with significant forwarded record counts:

 1SELECT
 2    OBJECT_SCHEMA_NAME(i.object_id)    AS schema_name,
 3    OBJECT_NAME(i.object_id)           AS table_name,
 4    i.forwarded_record_count,
 5    i.page_count,
 6    i.record_count
 7FROM sys.dm_db_index_physical_stats(
 8    DB_ID(), NULL, NULL, NULL, 'DETAILED') AS i
 9WHERE i.index_id = 0              -- heaps only
10  AND i.forwarded_record_count > 0
11ORDER BY i.forwarded_record_count DESC;

High forwarded record counts on a heap are a strong indicator that converting to a clustered index would reduce IO, even if adding a primary key is not strictly required for correctness.

Run the tables-without-primary-keys report after any database migration or after receiving a database from a third-party application vendor. Many packaged applications create tables without primary keys, and this is often the first schema quality check to run.

Conclusion

Tables without primary keys are heaps that accumulate performance debt over time through forwarded records, full table scans, and RID lookup overhead. This script surfaces them quickly so you can prioritise remediation based on table size. Address large no-PK tables first, and verify that any added primary key uses the most selective natural key available rather than defaulting to a surrogate identity column.

References

Posts in this series