Compare and synchronize the structure of tables in SQL Server databases

Tables

Compare and synchronize Tables.

Parameters of comparison and synchronization the structure of SQL Server database tables, including a column order, ANSI_NULLS, ANSI_PADDING and populating with values from Default Constraints or by default
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 template 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 run the prepare scripts before the comparison.

Rebuild

Rebuild (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 template 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 run the preparatory scripts before the comparison.

Drop

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

Column order

Align the order of columns.

ANSI_NULLS

If this option is not set, the comparison algorithm will ignore the ANSI_NULLS property of the tables. It is highly recommended to always use this option in the comparison.

ANSI_PADDING

If this option is not set, the comparison algorithm will ignore the ANSI_PADDING property of the tables. It is highly recommended to always use this option in the comparison.

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.


Drop and recreate a table to change the order of columns, ANSI_PADDING or ANSI_NULLS

When we need to drop and recreate a table, AutosyncDB uses the system stored procedure sp_rename to rename the existing table, create a new one with the same name, and then move the data to it and delete the old one. The call to sp_rename also causes the warning that should be ignored:

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

The call to sp_rename in this case can not harm the integrity of the database structure, because all of the children and referencing objects to the old table are already dropped and will be recreated but for the new table and the renamed table will be subsequently deleted.

Example of drop and recreate a table with comments

-- ... drop constraints, indexes, triggers, views and all other dependant objects if they are...
-- options
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 same 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##TEMP', 'OBJECT';
GO
-- create the 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
-- move the data, care about the IDENTITY field to correctly transfer it
SET IDENTITY_INSERT [dbo].[Events] ON;
GO
INSERT INTO [dbo].[Events] ([IdEvents], [Name], [Dsc], [Expires], [DateIns], [DateUpd], [DateDel])
SELECT [IdEvents], [Name], [Dsc], [Expires], [DateIns], [DateUpd], [DateDel]
	FROM [dbo].[Events##TEMP];
GO
SET IDENTITY_INSERT [dbo].[Events] OFF;
GO
-- restore the last IDENTITY value
DECLARE @seed bigint;
SET @seed = IDENT_CURRENT('[dbo].[Events##TEMP]');
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##TEMP])
	DROP TABLE [dbo].[Events##TEMP];
ELSE
	PRINT N' * ERROR: Lost data! Unable to copy rows from the old renamed table [dbo].[Events##TEMP] to the new.';
GO
--
-- ... create constraints, indexes, triggers, views and all other dependant objects if they are...