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...