Compare and synchronize the structure of tables in SQL Server databases

Settings for comparing and synchronizing table schemas of two SQL Server databases

Tables

Compare and synchronize Tables.

Create

Create new ones, if they do not exist in the target database.

Alter

Alter existing ones to remove the differences.

All columns that do not exist in the same table of the source database will be lost. If you need to rename a column you must provide your own handmade script to do this. Use the feature "Execute scripts" to execute the preparatory script before the comparison.

Recreate

Recreate (drop and create) existing ones to remove the differences. All the data will be transferred to the new table.

All columns that do not exist in the same table of the source database will be lost. If you need to rename a column you must provide your own handmade script for this. Use the feature "Execute scripts" to execute the preparatory script before the comparison.

Drop

Drop existing ones, if they do not exist in the source database. All data of the dropped tables will be lost.

Column order

Align the order of columns.

ANSI_NULLS

Set this option to change the ANSI_NULLS property of tables. Otherwise, the comparison algorithm will try inheriting these values ​for existing tables.

ANSI_PADDING

Set this option to change the ANSI_PADDING property of tables. Otherwise, the comparison algorithm will try to inherit these values​ for existing tables.

FILEGROUP / PARTITION SCHEMA

Set this option to change the filegroup (or partition schema) of tables, constraints, and indexes. Otherwise, the comparison algorithm will try to inherit these values ​​from existing objects. Appropriate filegroups and partition schemas must exist in the target database. The number of partitions may vary.

DATA_COMPRESSION

Set this option to change data compression of tables, constraints, and indexes. Otherwise, the comparison algorithm will try to inherit these values ​​from existing objects.

Even when filegroups or partition schemas different

Also change the data compression of objects that have different filegroups or partition schemas.

Use data compression type of last partition

If the target database has more partitions than the source, the remaining ones will use the data compression type of the last partition from the source database. Otherwise, the compression type of these partitions will not be taken into account.

Fill in new columns from Default Constraint

Populate the data of the new columns from theirs default constraint expressions.

Fill in NOT NULL columns from Default Constraint

Populate the data of the NOT NULL columns from theirs default constraint expressions.

Fill in NOT NULL columns with default values

Fill in the data of the NOT NULL columns by their type default values (like '', 0, 0x, '1900-01-01'), if there is not a default constraint or the appropriate option is not set.

Fill in '1900-01-01' instead of GETDATE(), etc.

Replace the functions that get the current date like GETDATE() on a minimum acceptable value of the date to fill in the column values.



Re-create tables approach

There are cases where the ALTER statement is not enough and the table must be completely re-created. For example, to change the order of columns, table type, ANSI_PADDING or ANSI_NULLS. AutosyncDB uses system stored procedure sp_rename to rename the existing table, create a new one with the original name, then copy the data into it and delete the renamed old one.

During the execution of such a script, a call to sp_rename raises a warning

Caution: Changing any part of an object name could break scripts and stored procedures.

that should be ignored. All child objects and objects referencing the old table are dropped before renaming, and then the renamed table will be dropped as well. Therefore, it cannot harm the integrity of the database.

Example of re-creating a table with comments

-- ... drop constraints, indexes, triggers, views and all other dependant objects if any...

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON;
GO
SET ANSI_PADDING ON;
GO
-- rename the existing table to create a new one with the original name
-- and catch the warning "Caution: Changing any part of an object name could break scripts and stored procedures."
EXECUTE sp_rename N'[dbo].[Events]', N'Events##old', 'OBJECT';
GO
-- create a new table
CREATE TABLE [dbo].[Events]
(
  [IdEvents] int NOT NULL IDENTITY,
  [Name] varchar(500) NULL,
  [Dsc] varchar(1000) NULL,
  [Expires] int NULL,
  [DateIns] datetime NOT NULL,
  [DateUpd] datetime NULL,
  [DateDel] datetime NULL
)
ON [PRIMARY]
GO
-- care about an IDENTITY column and copy the data
SET IDENTITY_INSERT [dbo].[Events] ON;
GO
DECLARE @error int;
INSERT INTO [dbo].[Events] ([IdEvents], [Name], [Dsc], [Expires], [DateIns], [DateUpd], [DateDel])
SELECT [IdEvents], [Name], [Dsc], [Expires], [DateIns], [DateUpd], [DateDel]
	FROM [dbo].[Events##old];
SET @error = @@ERROR;
IF @error <> 0
	RAISERROR(N'Possible data loss! The %d error occurred while copying data from the old renamed table [dbo].[Events##old] to the new one.', 16, 1, @error);
GO
SET IDENTITY_INSERT [dbo].[Events] OFF;
GO
-- restore the last IDENTITY value
DECLARE @seed bigint;
SET @seed = IDENT_CURRENT('[dbo].[Events##old]');
IF @seed IS NOT NULL
	DBCC CHECKIDENT('[dbo].[Events]', RESEED, @seed) WITH NO_INFOMSGS;
GO
-- drop the old table
IF (SELECT COUNT(1) FROM [dbo].[Events]) = (SELECT COUNT(1) FROM [dbo].[Events##old])
	DROP TABLE [dbo].[Events##old];
ELSE
	RAISERROR(N'Data loss! Unable to copy rows from the old renamed table [dbo].[Events##old] to the new one.', 16, 1);
GO
--
-- ... re-create constraints, indexes, triggers, views and all other dependant objects if any...