How to validate and synchronize schemas in SQL Server: step by step

João Barros 08 de September de 2026 6 min read

Validating and synchronizing schemas in SQL Server helps ensure that two databases (for example development and production) have consistent tables, columns and types. This prevents deployment errors and application failures that expect a specific schema.

Prerequisites

SQL Server (version compatible with sys.columns and INFORMATION_SCHEMA)
Read permissions on both databases to compare
SQL Server Management Studio (SSMS) or another SQL tool

Step 1: Why compare schemas and what differences to look for

Before synchronizing it is important to understand what can differ: missing tables, missing columns, different data types, different nullability, or extra columns. Validating prevents destructive changes and helps plan migration scripts.

Step 2: Create a basic comparison view between two databases

We will build a query that lists tables and columns from each database using INFORMATION_SCHEMA. Replace db_origem and db_destino with the actual names.

-- Ajuste db_origem e db_destino para os nomes reais das bases
DECLARE @db_origem SYSNAME = 'db_origem';
DECLARE @db_destino SYSNAME = 'db_destino';

SELECT
  o.TABLE_SCHEMA AS schema_origem,
  o.TABLE_NAME AS table_origem,
  o.COLUMN_NAME AS column_origem,
  o.DATA_TYPE AS type_origem,
  o.IS_NULLABLE AS nullable_origem,
  d.TABLE_SCHEMA AS schema_destino,
  d.TABLE_NAME AS table_destino,
  d.COLUMN_NAME AS column_destino,
  d.DATA_TYPE AS type_destino,
  d.IS_NULLABLE AS nullable_destino
FROM
  (SELECT * FROM [db_origem].INFORMATION_SCHEMA.COLUMNS) o
FULL OUTER JOIN
  (SELECT * FROM [db_destino].INFORMATION_SCHEMA.COLUMNS) d
ON
  o.TABLE_SCHEMA = d.TABLE_SCHEMA
  AND o.TABLE_NAME = d.TABLE_NAME
  AND o.COLUMN_NAME = d.COLUMN_NAME
ORDER BY COALESCE(o.TABLE_SCHEMA,d.TABLE_SCHEMA), COALESCE(o.TABLE_NAME,d.TABLE_NAME), COALESCE(o.ORDINAL_POSITION, d.ORDINAL_POSITION);

Step 3: Identify important differences

With the previous query, look for:

Records where column_origem IS NULL → table/column only in the destination database.
Records where column_destino IS NULL → only in the origin database.
Records where type_origem <> type_destino or nullability differs → incompatibilities.

Step 4: Generate synchronization script for missing tables/columns

Example of automatically generating ADD COLUMN on the destination for columns that exist in the origin and are missing in the destination. Review manually before executing.

DECLARE @db_origem SYSNAME = 'db_origem';
DECLARE @db_destino SYSNAME = 'db_destino';

SELECT
  'ALTER TABLE [' + c.TABLE_SCHEMA + '].[' + c.TABLE_NAME + '] ADD [' + c.COLUMN_NAME + '] ' +
    UPPER(c.DATA_TYPE) +
    CASE WHEN c.CHARACTER_MAXIMUM_LENGTH IS NOT NULL AND c.DATA_TYPE LIKE '%char%' THEN '(' +
      CASE WHEN c.CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX' ELSE CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) END +
    ')' ELSE '' END +
    CASE WHEN c.COLUMN_DEFAULT IS NOT NULL THEN ' DEFAULT ' + c.COLUMN_DEFAULT ELSE '' END +
    CASE WHEN c.IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE ' NULL' END AS add_column_sql
FROM
  [db_origem].INFORMATION_SCHEMA.COLUMNS c
LEFT JOIN
  [db_destino].INFORMATION_SCHEMA.COLUMNS d
  ON c.TABLE_SCHEMA = d.TABLE_SCHEMA AND c.TABLE_NAME = d.TABLE_NAME AND c.COLUMN_NAME = d.COLUMN_NAME
WHERE d.COLUMN_NAME IS NULL
ORDER BY c.TABLE_SCHEMA, c.TABLE_NAME, c.ORDINAL_POSITION;

Step 5: Handle incompatible types and NULL/NOT NULL

For type or nullability differences, do not automate blindly. Generate a report and then plan safe changes: create a new column with the correct type, copy data with CAST/CONVERT, test, then drop the old one and rename.

-- Exemplo de abordagem para mudar tipo: criar nova coluna, copiar e substituir
ALTER TABLE [schema].[tabela] ADD [col_nova] VARCHAR(100) NULL;
UPDATE [schema].[tabela] SET [col_nova] = CAST([col_antiga] AS VARCHAR(100));
-- Verificar integridade
ALTER TABLE [schema].[tabela] DROP COLUMN [col_antiga];
EXEC sp_rename '[schema].[tabela].[col_nova]', 'col_antiga', 'COLUMN';

Step 6: Automate regular checking with a job

Create a SQL Server Agent Job that runs the comparison query and sends results by email or logs them into an audit table. This way you detect divergences before they become production issues.

-- Exemplo simplificado: inserir diferenças numa tabela de auditoria
IF OBJECT_ID('dbo.SchemaDiffAudit') IS NULL
CREATE TABLE dbo.SchemaDiffAudit (
  AuditDate DATETIME2, TableSchema SYSNAME, TableName SYSNAME, ColumnName SYSNAME, Issue NVARCHAR(200)
);

INSERT INTO dbo.SchemaDiffAudit (AuditDate, TableSchema, TableName, ColumnName, Issue)
SELECT GETDATE(), COALESCE(o.TABLE_SCHEMA,d.TABLE_SCHEMA), COALESCE(o.TABLE_NAME,d.TABLE_NAME),
  COALESCE(o.COLUMN_NAME,d.COLUMN_NAME),
  CASE
    WHEN o.COLUMN_NAME IS NULL THEN 'Only in dest'
    WHEN d.COLUMN_NAME IS NULL THEN 'Only in origin'
    WHEN o.DATA_TYPE <> d.DATA_TYPE THEN 'Type mismatch: ' + o.DATA_TYPE + ' vs ' + d.DATA_TYPE
    WHEN o.IS_NULLABLE <> d.IS_NULLABLE THEN 'Nullability mismatch'
    ELSE 'Other'
  END
FROM [db_origem].INFORMATION_SCHEMA.COLUMNS o
FULL OUTER JOIN [db_destino].INFORMATION_SCHEMA.COLUMNS d
ON o.TABLE_SCHEMA = d.TABLE_SCHEMA AND o.TABLE_NAME = d.TABLE_NAME AND o.COLUMN_NAME = d.COLUMN_NAME
WHERE o.COLUMN_NAME IS NULL OR d.COLUMN_NAME IS NULL OR o.DATA_TYPE <> d.DATA_TYPE OR o.IS_NULLABLE <> d.IS_NULLABLE;

Verify the result

Review the output of the queries: the list should show only actual differences. After running synchronization scripts, run the comparison again — the number of discrepancies should drop to zero. Test dependent applications to ensure there are no regressions.

Conclusion

Comparing and synchronizing schemas in SQL Server reduces deployment risks and keeps consistency between environments. Next steps: create backups before changes, test in a staging environment and integrate the check into the CI/CD process. Tip: always manually validate automatically generated scripts before executing them in production.