Compare the structure of two databases

An application works in development, but fails to deploy because a column has a different length, allows null values, or uses a different type in production. Going through the tables one by one makes it easy to miss a small difference.

What we need to distinguish

The schema is the structural definition of database objects. For numeric types, precision is the total number of digits and scale is the decimal digits; nullability indicates whether a column accepts NULL.

We will compare columns by schema, table and name. The result will separate missing columns of different definitions and display type, length, precision, scale, and nullability.

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
WITH ColumnasOrigen AS
(
    SELECT
        s.name COLLATE DATABASE_DEFAULT AS Esquema,
        t.name COLLATE DATABASE_DEFAULT AS Tabla,
        c.name COLLATE DATABASE_DEFAULT AS Columna,
        ty.name COLLATE DATABASE_DEFAULT AS Tipo,
        c.max_length AS LongitudBytes,
        c.precision AS Precision,
        c.scale AS Escala,
        c.is_nullable AS PermiteNulos
    FROM BaseDesarrollo.sys.tables AS t
    INNER JOIN BaseDesarrollo.sys.schemas AS s
        ON s.schema_id = t.schema_id
    INNER JOIN BaseDesarrollo.sys.columns AS c
        ON c.object_id = t.object_id
    INNER JOIN BaseDesarrollo.sys.types AS ty
        ON ty.user_type_id = c.user_type_id
),
ColumnasDestino AS
(
    SELECT
        s.name COLLATE DATABASE_DEFAULT AS Esquema,
        t.name COLLATE DATABASE_DEFAULT AS Tabla,
        c.name COLLATE DATABASE_DEFAULT AS Columna,
        ty.name COLLATE DATABASE_DEFAULT AS Tipo,
        c.max_length AS LongitudBytes,
        c.precision AS Precision,
        c.scale AS Escala,
        c.is_nullable AS PermiteNulos
    FROM BaseProduccion.sys.tables AS t
    INNER JOIN BaseProduccion.sys.schemas AS s
        ON s.schema_id = t.schema_id
    INNER JOIN BaseProduccion.sys.columns AS c
        ON c.object_id = t.object_id
    INNER JOIN BaseProduccion.sys.types AS ty
        ON ty.user_type_id = c.user_type_id
)
SELECT
    COALESCE(o.Esquema, d.Esquema) AS Esquema,
    COALESCE(o.Tabla, d.Tabla) AS Tabla,
    COALESCE(o.Columna, d.Columna) AS Columna,
    CASE
        WHEN o.Columna IS NULL THEN N'Solo en destino'
        WHEN d.Columna IS NULL THEN N'Solo en origen'
        ELSE N'Definición diferente'
    END AS Diferencia,
    o.Tipo AS TipoOrigen,
    d.Tipo AS TipoDestino,
    o.LongitudBytes AS LongitudOrigen,
    d.LongitudBytes AS LongitudDestino,
    o.Precision AS PrecisionOrigen,
    d.Precision AS PrecisionDestino,
    o.Escala AS EscalaOrigen,
    d.Escala AS EscalaDestino,
    o.PermiteNulos AS NulosOrigen,
    d.PermiteNulos AS NulosDestino
FROM ColumnasOrigen AS o
FULL OUTER JOIN ColumnasDestino AS d
    ON d.Esquema = o.Esquema
   AND d.Tabla = o.Tabla
   AND d.Columna = o.Columna
WHERE o.Columna IS NULL
   OR d.Columna IS NULL
   OR EXISTS
      (
          SELECT
              o.Tipo, o.LongitudBytes, o.Precision,
              o.Escala, o.PermiteNulos
          EXCEPT
          SELECT
              d.Tipo, d.LongitudBytes, d.Precision,
              d.Escala, d.PermiteNulos
      )
ORDER BY
    Esquema,
    Tabla,
    Columna;
GO

Parameters or permissions

Replace BaseDevelopment and BaseProduction with bases of the same instance. Runs the query from a base whose collation can be used to compare names; COLLATE DATABASE_DEFAULT avoids a common conflict when bases use different collations. Metadata visibility is needed in both databases. max_length is expressed in bytes: in nvarchar and nchar, except max, it usually corresponds to twice the number of characters.

How to read the result

EXCEPT compares complete projections and returns rows present in the first set but not the second.

A column present in a single database appears as “Source only” or “Destination only”. If it exists in both, it is displayed only when at least one compared property changes. EXCEPT allows you to also compare null values without constructing a separate condition per property. The columns of both SELECTs retain the same order and compatible types. The query compares column structure, not data. It also does not attempt to generate ALTER statements; We first need to decide which definition is the expected one and what impact modifying it would have.

What this does establish

Allows you to locate missing columns and differences in included structural properties between two accessible databases of the same instance.

What this still does not establish

It does not compare IDENTITY, calculated columns, indexes, keys, constraints, default values, triggers, permissions or SQL code. User-defined types also require reviewing their schema and definition, not just the display name.

Next reasonable check

Classify each difference as expected or pending deployment. Then review dependencies, existing data, and migration scripts before proposing a structure change.

Search SQL Server