Comparing Two Schemas With sys.columns and a FULL OUTER JOIN

Databases that started alike can quietly acquire different columns, indexes, and procedures. Comparing two schemas with catalog queries turns that uncertainty into a reviewable list before anyone writes an alteration script.

Define the Scope Before Comparing Two Schemas

The examples compare accessible databases named SalesA and SalesB on the same SQL Server instance. Replace those names with the approved source and target. The queries create session-local temporary tables for collected metadata but do not change either database's schema. Use an identity with permission to see the relevant definitions.

I begin by defining which schemas and object types belong in the review. System objects, deliberate environment differences, and deployment support objects need an explicit policy. Otherwise a useful drift report becomes a collection of expected exceptions. A missing object in your results can also mean missing metadata visibility, so verify access before declaring deletion.

Use a consistent comparison collation when database collations differ. The examples normalize names to a specified case-sensitive collation so case differences remain visible. Choose the rule your deployment uses and explain it. Database-specific object IDs are local identifiers; identical numbers in two databases do not establish object identity across them.

Find Tables Missing From Either Side

Match tables by schema and table name. Include the schema because two objects named Orders can exist independently in different schemas. A FULL OUTER JOIN retains both unmatched populations, which prevents the comparison from hiding whichever side happens to be placed on the right.

WITH A AS
(
    SELECT s.name COLLATE Latin1_General_100_CS_AS AS SchemaName,
           t.name COLLATE Latin1_General_100_CS_AS AS TableName
    FROM SalesA.sys.tables AS t
    JOIN SalesA.sys.schemas AS s ON s.schema_id = t.schema_id
    WHERE t.is_ms_shipped = 0
), B AS
(
    SELECT s.name COLLATE Latin1_General_100_CS_AS AS SchemaName,
           t.name COLLATE Latin1_General_100_CS_AS AS TableName
    FROM SalesB.sys.tables AS t
    JOIN SalesB.sys.schemas AS s ON s.schema_id = t.schema_id
    WHERE t.is_ms_shipped = 0
)
SELECT COALESCE(A.SchemaName,B.SchemaName) AS SchemaName,
       COALESCE(A.TableName,B.TableName) AS TableName,
       CASE WHEN A.TableName IS NULL THEN 'Only B'
            WHEN B.TableName IS NULL THEN 'Only A' END AS Difference
FROM A FULL OUTER JOIN B
  ON A.SchemaName = B.SchemaName AND A.TableName = B.TableName
WHERE A.TableName IS NULL OR B.TableName IS NULL;

This includes empty tables, which a column-only report can overlook in a broader object inventory. Keep the direction labels consistent throughout the package. Only A describes a difference; it does not decide whether B should receive a CREATE TABLE or A should lose a table.

Collect Comparable Column Details

For columns, include the type name and its schema as well as length, precision, scale, and nullability. User-defined type identifiers cannot be compared numerically across databases. A character column's max_length is stored in bytes, and minus one represents a maximum-length type. Preserve those values for a faithful metadata comparison.

SELECT s.name COLLATE Latin1_General_100_CS_AS AS SchemaName,
       t.name COLLATE Latin1_General_100_CS_AS AS TableName,
       c.name COLLATE Latin1_General_100_CS_AS AS ColumnName,
       ts.name COLLATE Latin1_General_100_CS_AS AS TypeSchema,
       ty.name COLLATE Latin1_General_100_CS_AS AS TypeName,
       c.max_length, c.precision, c.scale, c.is_nullable
INTO #ColumnsA
FROM SalesA.sys.tables AS t
JOIN SalesA.sys.schemas AS s ON s.schema_id = t.schema_id
JOIN SalesA.sys.columns AS c ON c.object_id = t.object_id
JOIN SalesA.sys.types AS ty ON ty.user_type_id = c.user_type_id
JOIN SalesA.sys.schemas AS ts ON ts.schema_id = ty.schema_id
WHERE t.is_ms_shipped = 0;
SELECT s.name COLLATE Latin1_General_100_CS_AS AS SchemaName,
       t.name COLLATE Latin1_General_100_CS_AS AS TableName,
       c.name COLLATE Latin1_General_100_CS_AS AS ColumnName,
       ts.name COLLATE Latin1_General_100_CS_AS AS TypeSchema,
       ty.name COLLATE Latin1_General_100_CS_AS AS TypeName,
       c.max_length, c.precision, c.scale, c.is_nullable
INTO #ColumnsB
FROM SalesB.sys.tables AS t
JOIN SalesB.sys.schemas AS s ON s.schema_id = t.schema_id
JOIN SalesB.sys.columns AS c ON c.object_id = t.object_id
JOIN SalesB.sys.types AS ty ON ty.user_type_id = c.user_type_id
JOIN SalesB.sys.schemas AS ts ON ts.schema_id = ty.schema_id
WHERE t.is_ms_shipped = 0;

Run the collection in a new session or remove your own previous temporary tables first. These snapshots are taken at different instants. Coordinate a quiet deployment window when exact simultaneous comparison matters, and retain capture times with a persisted review package.

Join Columns and Label Differences

The following comparison identifies missing columns separately from changed definitions. Its selected properties have known non-NULL metadata values, so direct inequality is sufficient for these fields. When extending the query with nullable collation or expression properties, add explicit NULL-aware comparison rather than relying on inequality alone.

SELECT COALESCE(A.SchemaName,B.SchemaName) AS SchemaName,
       COALESCE(A.TableName,B.TableName) AS TableName,
       COALESCE(A.ColumnName,B.ColumnName) AS ColumnName,
       CASE WHEN A.ColumnName IS NULL THEN 'Only B'
            WHEN B.ColumnName IS NULL THEN 'Only A'
            ELSE 'Definition differs' END AS Difference,
       A.TypeName AS TypeA, B.TypeName AS TypeB,
       A.max_length AS LengthA, B.max_length AS LengthB,
       A.is_nullable AS NullableA, B.is_nullable AS NullableB
FROM #ColumnsA AS A FULL OUTER JOIN #ColumnsB AS B
  ON A.SchemaName = B.SchemaName AND A.TableName = B.TableName
 AND A.ColumnName = B.ColumnName
WHERE A.ColumnName IS NULL OR B.ColumnName IS NULL
   OR A.TypeSchema <> B.TypeSchema OR A.TypeName <> B.TypeName
   OR A.max_length <> B.max_length OR A.precision <> B.precision
   OR A.scale <> B.scale OR A.is_nullable <> B.is_nullable;

Comparing two schemas should not stop at the abbreviated display columns. The predicate also checks precision and scale even though this compact output does not print them. Add those values to the display when investigating decimal differences. Name the comparison scope accurately instead of presenting a partial report as exhaustive equivalence.

Comparing Two Schemas at the Index Level

Indexes need their key order, direction, included columns, uniqueness, filter, and type compared. A matching name does not establish a matching access path. This SQL Server 2017 or later example inventory returns one row per index column and repeats the index properties, making the ordered structure directly inspectable.

SELECT s.name COLLATE Latin1_General_100_CS_AS AS SchemaName, t.name COLLATE Latin1_General_100_CS_AS AS TableName,
       i.name COLLATE Latin1_General_100_CS_AS AS IndexName, i.type_desc COLLATE Latin1_General_100_CS_AS AS type_desc, i.is_unique,
       i.is_disabled, i.has_filter, i.filter_definition COLLATE Latin1_General_100_CS_AS AS filter_definition,
       c.name COLLATE Latin1_General_100_CS_AS AS ColumnName, ic.key_ordinal,
       ic.is_descending_key, ic.is_included_column
INTO #IndexesA
FROM SalesA.sys.tables AS t
JOIN SalesA.sys.schemas AS s ON s.schema_id = t.schema_id
JOIN SalesA.sys.indexes AS i ON i.object_id = t.object_id
LEFT JOIN SalesA.sys.index_columns AS ic
  ON ic.object_id = i.object_id AND ic.index_id = i.index_id
LEFT JOIN SalesA.sys.columns AS c
  ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE t.is_ms_shipped = 0 AND i.index_id > 0;
SELECT s.name COLLATE Latin1_General_100_CS_AS AS SchemaName, t.name COLLATE Latin1_General_100_CS_AS AS TableName,
       i.name COLLATE Latin1_General_100_CS_AS AS IndexName, i.type_desc COLLATE Latin1_General_100_CS_AS AS type_desc, i.is_unique,
       i.is_disabled, i.has_filter, i.filter_definition COLLATE Latin1_General_100_CS_AS AS filter_definition,
       c.name COLLATE Latin1_General_100_CS_AS AS ColumnName, ic.key_ordinal,
       ic.is_descending_key, ic.is_included_column
INTO #IndexesB
FROM SalesB.sys.tables AS t
JOIN SalesB.sys.schemas AS s ON s.schema_id = t.schema_id
JOIN SalesB.sys.indexes AS i ON i.object_id = t.object_id
LEFT JOIN SalesB.sys.index_columns AS ic
  ON ic.object_id = i.object_id AND ic.index_id = i.index_id
LEFT JOIN SalesB.sys.columns AS c
  ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE t.is_ms_shipped = 0 AND i.index_id > 0;
SELECT 'Only A or changed A' AS Difference, d.*
FROM (SELECT * FROM #IndexesA EXCEPT SELECT * FROM #IndexesB) AS d
UNION ALL
SELECT 'Only B or changed B' AS Difference, d.*
FROM (SELECT * FROM #IndexesB EXCEPT SELECT * FROM #IndexesA) AS d;

The query collects both inventories and returns metadata rows that differ in either direction. Compare the ordered key positions and treat included columns as a set. Include partitioning, compression, and storage placement when those properties belong in the deployment contract. A different index name can still describe equivalent structure. A primary key created without a name gets a system-generated name in each database, so it shows up on both sides even when its columns match.

Review Properties Beyond Column Type

Two columns can match in type and nullability while enforcing different behavior. Review defaults, check constraints, computed expressions, identity settings, and column collations as separate metadata dimensions. A default changes future inserts, while a check constraint changes which writes are accepted. Neither difference appears in the compact type comparison above.

Foreign keys also need their referenced table and column order, enabled state, and trust state examined. A constraint with the same name can point to a different target. An untrusted constraint has a different optimizer relationship from one whose existing rows have been validated. Preserve the full definition and state when documenting the intended change.

Temporal configuration, generated columns, partitioning, and specialized index types deserve dedicated checks when they occur in the reviewed database. Start with an explicit inventory of those features. The method scales by adding known dimensions, rather than assuming a short catalog join silently covers every feature that SQL Server supports. Completeness is a declared scope, not a long-looking query.

Hash and Review Module Definitions

Collect module text inside each database context so OBJECT_DEFINITION resolves the local object ID correctly. The hash quickly identifies text differences, but NULL definitions remain unknown and must be reported separately. This method compares definition bytes, rather than proving that two differently formatted modules behave differently.

USE SalesA;
GO
SELECT s.name COLLATE Latin1_General_100_CS_AS AS SchemaName,
       o.name COLLATE Latin1_General_100_CS_AS AS ObjectName,
       HASHBYTES('SHA2_256',OBJECT_DEFINITION(o.object_id)) AS DefinitionHash
INTO #ModulesA
FROM sys.objects AS o
JOIN sys.schemas AS s ON s.schema_id = o.schema_id
JOIN sys.sql_modules AS m ON m.object_id = o.object_id
WHERE o.is_ms_shipped = 0;
USE SalesB;
GO
SELECT s.name COLLATE Latin1_General_100_CS_AS AS SchemaName,
       o.name COLLATE Latin1_General_100_CS_AS AS ObjectName,
       HASHBYTES('SHA2_256',OBJECT_DEFINITION(o.object_id)) AS DefinitionHash
INTO #ModulesB
FROM sys.objects AS o
JOIN sys.schemas AS s ON s.schema_id = o.schema_id
JOIN sys.sql_modules AS m ON m.object_id = o.object_id
WHERE o.is_ms_shipped = 0;
SELECT COALESCE(A.SchemaName,B.SchemaName) AS SchemaName,
       COALESCE(A.ObjectName,B.ObjectName) AS ObjectName,
       A.DefinitionHash AS HashA, B.DefinitionHash AS HashB
FROM #ModulesA AS A FULL OUTER JOIN #ModulesB AS B
  ON A.SchemaName = B.SchemaName AND A.ObjectName = B.ObjectName
WHERE A.ObjectName IS NULL OR B.ObjectName IS NULL
   OR A.DefinitionHash IS NULL OR B.DefinitionHash IS NULL
   OR A.DefinitionHash <> B.DefinitionHash;

I inspect the actual text after a hash difference and also compare saved module options where required. Which difference represents an approved environment choice? Answer that before generating DDL. Comparing two schemas produces evidence for a decision; it does not authorize synchronizing one database to the other.

Turn Differences From Comparing Two Schemas Into Decisions

Retain each difference with the source and target identities, collection time, and comparison rule. Then attach an owner decision: expected, requires investigation, or approved for a particular change. A report becomes useful when someone can explain why each difference exists and what should happen next.

Consider deployment dependencies before drafting any correction. A smaller character length can reject existing values. A new NOT NULL rule requires a plan for current NULLs. Replacing an index affects writes and queries, and replacing module text can require permission and signature handling. Rehearse approved changes with representative data instead of deriving an executable deployment directly from the report.

Repeat the read-only comparison after an approved deployment to confirm the intended properties and identify residual differences. Keep expected exceptions visible so they do not repeatedly consume review time. Store current findings separately from historical findings; an old drift report cannot establish today's state. That discipline makes the next comparison quicker without silently reducing its coverage.

Related reading on this blog: Comparing Two Database Schemas With T-SQL and Comparing Data Between Two Tables.

A schema difference is not an automatic deployment instruction, it is evidence that needs an explicit owner decision.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.