Two databases claim to have the same schema, but a deployment behaves differently in each. Comparing two database schemas with T-SQL can show the specific differences instead of relying on memory.
Choose the Scope Before Comparing Two Database Schemas
Decide whether the task is to compare tables, views, procedures, columns, indexes, constraints, or permissions. Comparing two database schemas in full is a large task. Start with the objects related to a deployment or incident, then widen the scope. Record source and target database names.
I compare names and definitions separately. An object can exist in both databases with different columns. A column can match by name but differ in type or nullability. A simple object count hides these differences.
Run the queries under an identity with metadata visibility in both databases. A missing permission can make an object appear absent. Label a partial comparison rather than treating inaccessible metadata as drift.
SELECT DB_NAME() AS CurrentDatabase,
HAS_DBACCESS(N'DatabaseA') AS AccessA,
HAS_DBACCESS(N'DatabaseB') AS AccessB;Find Missing Objects by Comparing Two Database Schemas
sys.objects and sys.schemas provide schema, name, and type. A FULL JOIN between inventories reveals objects present on one side only. Filter out internal and system objects according to the comparison purpose. Use schema and type in the key, not just object name.
I review missing objects before examining details. A missing view can explain a failed report directly. A table with the same name in another schema is not the same contract.
The example uses two databases on one instance. If databases live on separate servers, collect each inventory into local staging tables through an approved method, then compare those tables. Do not assume a three-part name can cross servers.
WITH a AS
(
SELECT s.name AS SchemaName, o.name AS ObjectName, o.type
FROM DatabaseA.sys.objects AS o
JOIN DatabaseA.sys.schemas AS s ON s.schema_id = o.schema_id
WHERE o.is_ms_shipped = 0
),
b AS
(
SELECT s.name AS SchemaName, o.name AS ObjectName, o.type
FROM DatabaseB.sys.objects AS o
JOIN DatabaseB.sys.schemas AS s ON s.schema_id = o.schema_id
WHERE o.is_ms_shipped = 0
)
SELECT COALESCE(a.SchemaName COLLATE DATABASE_DEFAULT,
b.SchemaName COLLATE DATABASE_DEFAULT) AS SchemaName,
COALESCE(a.ObjectName COLLATE DATABASE_DEFAULT,
b.ObjectName COLLATE DATABASE_DEFAULT) AS ObjectName,
a.type AS TypeA, b.type AS TypeB
FROM a FULL JOIN b
ON a.SchemaName COLLATE DATABASE_DEFAULT =
b.SchemaName COLLATE DATABASE_DEFAULT
AND a.ObjectName COLLATE DATABASE_DEFAULT =
b.ObjectName COLLATE DATABASE_DEFAULT
AND a.type=b.type
WHERE a.ObjectName IS NULL OR b.ObjectName IS NULL;Compare Column Contracts
Join sys.columns through sys.tables and sys.schemas in each database. Compare column name, data type, length, precision, scale, nullability, and identity property when relevant. A varchar length difference can change truncation behavior. A nullable difference can change inserts.
I keep object and column names in the result. A raw column_id is only an internal ordinal, not the business identity of a column. Review computed columns and collations separately when they matter to the release.
Do not treat max_length as characters for every type. nvarchar length is represented in bytes. Translate it correctly for display, or show the raw metadata field and type together. A misleading comparison query can create false drift.
WITH a AS
(
SELECT s.name AS SchemaName, t.name AS TableName,
c.name AS ColumnName, ty.name AS DataType,
c.max_length, c.precision, c.scale, c.is_nullable
FROM DatabaseA.sys.tables AS t
JOIN DatabaseA.sys.schemas AS s ON s.schema_id = t.schema_id
JOIN DatabaseA.sys.columns AS c ON c.object_id = t.object_id
JOIN DatabaseA.sys.types AS ty ON ty.user_type_id = c.user_type_id
),
b AS
(
SELECT s.name AS SchemaName, t.name AS TableName,
c.name AS ColumnName, ty.name AS DataType,
c.max_length, c.precision, c.scale, c.is_nullable
FROM DatabaseB.sys.tables AS t
JOIN DatabaseB.sys.schemas AS s ON s.schema_id = t.schema_id
JOIN DatabaseB.sys.columns AS c ON c.object_id = t.object_id
JOIN DatabaseB.sys.types AS ty ON ty.user_type_id = c.user_type_id
)
SELECT COALESCE(a.SchemaName COLLATE DATABASE_DEFAULT,
b.SchemaName COLLATE DATABASE_DEFAULT) AS SchemaName,
COALESCE(a.TableName COLLATE DATABASE_DEFAULT,
b.TableName COLLATE DATABASE_DEFAULT) AS TableName,
COALESCE(a.ColumnName COLLATE DATABASE_DEFAULT,
b.ColumnName COLLATE DATABASE_DEFAULT) AS ColumnName,
a.DataType AS TypeA, b.DataType AS TypeB,
a.max_length AS LengthA, b.max_length AS LengthB,
a.precision AS PrecisionA, b.precision AS PrecisionB,
a.scale AS ScaleA, b.scale AS ScaleB,
a.is_nullable AS NullableA, b.is_nullable AS NullableB
FROM a FULL JOIN b
ON a.SchemaName COLLATE DATABASE_DEFAULT =
b.SchemaName COLLATE DATABASE_DEFAULT
AND a.TableName COLLATE DATABASE_DEFAULT =
b.TableName COLLATE DATABASE_DEFAULT
AND a.ColumnName COLLATE DATABASE_DEFAULT =
b.ColumnName COLLATE DATABASE_DEFAULT
WHERE a.ColumnName IS NULL OR b.ColumnName IS NULL
OR a.DataType COLLATE DATABASE_DEFAULT IS DISTINCT FROM
b.DataType COLLATE DATABASE_DEFAULT
OR a.max_length IS DISTINCT FROM b.max_length
OR a.precision IS DISTINCT FROM b.precision
OR a.scale IS DISTINCT FROM b.scale
OR a.is_nullable IS DISTINCT FROM b.is_nullable
ORDER BY SchemaName, TableName, ColumnName;
Compare Index Definitions
Index names alone are not enough. Compare uniqueness, filter predicate, key column order, included columns, and sort direction. A matching name can hide a different definition. A different name can describe the same useful structure.
I build an index inventory for each side, then compare normalized definitions. For a first pass, sys.indexes can show names and core flags. Follow with sys.index_columns and sys.columns for keys and includes. Inspect partitioning and compression when those affect workload.
An index difference can be intentional. A reporting database can need an extra index that production does not. Put expected differences on a list so they do not recur as surprises during every review.
WITH a AS
(
SELECT s.name AS SchemaName, t.name AS TableName,
i.name AS IndexName, c.name AS ColumnName,
i.is_unique, i.filter_definition,
ic.key_ordinal, ic.is_included_column, ic.is_descending_key
FROM DatabaseA.sys.indexes AS i
JOIN DatabaseA.sys.tables AS t ON t.object_id = i.object_id
JOIN DatabaseA.sys.schemas AS s ON s.schema_id = t.schema_id
JOIN DatabaseA.sys.index_columns AS ic
ON ic.object_id = i.object_id AND ic.index_id = i.index_id
JOIN DatabaseA.sys.columns AS c
ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE i.index_id > 0
),
b AS
(
SELECT s.name AS SchemaName, t.name AS TableName,
i.name AS IndexName, c.name AS ColumnName,
i.is_unique, i.filter_definition,
ic.key_ordinal, ic.is_included_column, ic.is_descending_key
FROM DatabaseB.sys.indexes AS i
JOIN DatabaseB.sys.tables AS t ON t.object_id = i.object_id
JOIN DatabaseB.sys.schemas AS s ON s.schema_id = t.schema_id
JOIN DatabaseB.sys.index_columns AS ic
ON ic.object_id = i.object_id AND ic.index_id = i.index_id
JOIN DatabaseB.sys.columns AS c
ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE i.index_id > 0
)
SELECT COALESCE(a.SchemaName COLLATE DATABASE_DEFAULT,
b.SchemaName COLLATE DATABASE_DEFAULT) AS SchemaName,
COALESCE(a.TableName COLLATE DATABASE_DEFAULT,
b.TableName COLLATE DATABASE_DEFAULT) AS TableName,
COALESCE(a.IndexName COLLATE DATABASE_DEFAULT,
b.IndexName COLLATE DATABASE_DEFAULT) AS IndexName,
COALESCE(a.ColumnName COLLATE DATABASE_DEFAULT,
b.ColumnName COLLATE DATABASE_DEFAULT) AS ColumnName,
a.key_ordinal AS KeyOrdinalA, b.key_ordinal AS KeyOrdinalB,
a.is_included_column AS IncludedA,
b.is_included_column AS IncludedB,
a.is_descending_key AS DescendingA,
b.is_descending_key AS DescendingB,
a.is_unique AS UniqueA, b.is_unique AS UniqueB,
a.filter_definition AS FilterA, b.filter_definition AS FilterB
FROM a FULL JOIN b
ON a.SchemaName COLLATE DATABASE_DEFAULT =
b.SchemaName COLLATE DATABASE_DEFAULT
AND a.TableName COLLATE DATABASE_DEFAULT =
b.TableName COLLATE DATABASE_DEFAULT
AND a.IndexName COLLATE DATABASE_DEFAULT =
b.IndexName COLLATE DATABASE_DEFAULT
AND a.ColumnName COLLATE DATABASE_DEFAULT =
b.ColumnName COLLATE DATABASE_DEFAULT
WHERE a.ColumnName IS NULL OR b.ColumnName IS NULL
OR a.key_ordinal IS DISTINCT FROM b.key_ordinal
OR a.is_included_column IS DISTINCT FROM b.is_included_column
OR a.is_descending_key IS DISTINCT FROM b.is_descending_key
OR a.is_unique IS DISTINCT FROM b.is_unique
OR a.filter_definition COLLATE DATABASE_DEFAULT IS DISTINCT FROM
b.filter_definition COLLATE DATABASE_DEFAULT
ORDER BY SchemaName, TableName, IndexName, ColumnName;Include Constraints and Modules
Primary keys, foreign keys, defaults, and CHECK constraints affect behavior even when columns match. Compare their definitions and trusted status. Views and procedures need text or normalized definition comparison, but formatting differences can produce false positives.
I classify differences as missing, changed behavior, changed performance, and naming only. That makes the output useful for a release plan. A different default constraint name can break a deployment script even if the expression matches.
Do not automatically copy all source definitions into target. One side can contain a deliberate hotfix or tenant-specific change. Verify authority and desired state before generating ALTER statements. The comparison tells you what differs, not which side is right.
Control for Environment Settings When Comparing Two Database Schemas
Database compatibility level, collation, and scoped configuration can affect behavior without a table difference. Compare them when a query performs differently across environments. Permissions and users also vary by purpose. Include them only under a clear security comparison scope.
I save the inventory timestamp. Schemas can change during a long comparison, making one side a moving target. Use a planned quiet window or consistent deployment snapshot when accuracy matters. A comparison run should say when it observed each database.
Test the comparison queries themselves on known differences. Add one test column or index in a disposable database and confirm the report finds it. A diff script that returns no rows can be broken as well as reassuring.
SELECT name, compatibility_level, collation_name
FROM sys.databases
WHERE name IN (N'DatabaseA', N'DatabaseB');Turn the Diff Into a Reviewed Change
Group findings by object and owner. Identify the intended target state, dependency order, and validation query. A changed column can require data conversion and application coordination. A missing index can require build time and storage. The diff is evidence, not a deployment script by itself.
I keep the original inventories with the release record. After the change, rerun the same comparison and review remaining intentional differences. That closes the loop without assuming every difference should vanish.
T-SQL catalog queries are excellent for comparing two database schemas with a focused scope. They are most useful when metadata coverage, object identity, and expected differences are explicit. The goal is a clear change plan, not merely an empty diff grid.
Object names alone miss important schema differences. Compare column order where a consumer depends on SELECT *, data types with length and scale, nullability, defaults, computed definitions, and collation. Which differences change behavior, and which are harmless naming conventions? I review the catalog result with the application contract in mind instead of treating every row as equal severity.
Indexes also need more than a name comparison. Key order, included columns, filters, uniqueness, and partition placement affect behavior and cost. A query that reports one index on each side can still miss a meaningful difference in definition. For a deployment, script the intended change and rehearse it on a copy. A comparison query diagnoses drift. It does not decide that the source side should overwrite the target.
Related reading on this blog: How to Compare the Schema of Two Databases with Schema Compare and Comparing Two Databases Without a Scoreboard.
A schema diff is not a deployment decision, it is evidence about two database contracts.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
