Find tables without primary key

An update needs to identify each row unambiguously, but we don’t know which tables declare a primary key. We also want to locate structures that deserve review before an integration or migration.

What we need to distinguish

A primary key identifies each row uniquely and does not accept NULL. A unique index prevents its value combination from repeating but does not by itself declare the table’s primary identity.

We will search for user tables without a PRIMARY KEY constraint. We will also indicate if there is a unique index, because a table can lack a primary key and still protect a combination of values.

Query

/*
    Antes de ejecutar:
        Revisa la base activa, los nombres de objetos, los parámetros y los permisos.
        Adapta el ejemplo a tu modelo y pruébalo primero en un ambiente controlado.
*/

SET NOCOUNT ON;
GO
-- Consulta
SELECT
    s.name AS Esquema,
    t.name AS Tabla,
    CASE
        WHEN EXISTS
        (
            SELECT 1
            FROM sys.indexes AS i
            WHERE i.object_id = t.object_id
              AND i.is_unique = 1
              AND i.is_hypothetical = 0
        ) THEN 1
        ELSE 0
    END AS TieneIndiceUnico,
    t.create_date AS FechaCreacion,
    t.modify_date AS FechaModificacion
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
    ON s.schema_id = t.schema_id
WHERE t.is_ms_shipped = 0
  AND t.temporal_type <> 1
  AND NOT EXISTS
  (
      SELECT 1
      FROM sys.key_constraints AS kc
      WHERE kc.parent_object_id = t.object_id
        AND kc.type = 'PK'
  )
ORDER BY
    s.name,
    t.name;
GO

Parameters or permissions

The query works on the current basis and requires metadata visibility. temporal_type = 1 identifies a temporary history table; is excluded because these tables have special restrictions and should not be treated as an ordinary keyless table. If you need to check external, memory-optimized tables, graph tables, or other specialized structures, add their flags to the result and evaluate them separately.

How to read the result

Each row represents a table with no PK type restriction. HasUniqueIndex = 1 indicates that at least one unique index exists, but does not claim that its columns form the correct functional identity or that all of them allow the expected behavior with null values. A primary key also creates a unique index to enforce the constraint, although the index can be clustered or nonclustered. Here we query sys.key_constraints to distinguish the declared rule from any unique index. Dates help guide the review, but modify_date can change due to index operations and is not reliable design history.

What this does establish

Allows you to enumerate visible tables that do not declare a primary key and recognize which ones have at least one unique index.

What this still does not establish

A staging table holds preparatory data before validation or transfer to its destination; it may follow different rules from a transactional table.

It does not prove that a table contains duplicates, is poorly designed, or that adding a key is safe. Staging tables, queues or work structures may have specific reasons for not declaring it. It also does not automatically identify the columns that should form the key.

Next reasonable check

Review the purpose and volume of each candidate table. Searches for duplicates on the proposed business key, identifies existing references and checks for nullability before designing a constraint.

Search SQL Server