SQL Server : Script to Find and Create Missing Tables for Database Sychronization

Different methods and tools are available to synchronize two SQL Server databases. But one simple way to synchronize is using TSQL Script to find difference and create missing structures. Following script is for the same purpose. Script can be used to find and create missing tables on target database by comparing tables structure with source database.

	--DROP IF ALREADY CREATED
	IF EXISTS (SELECT 1 FROM sys.objects WHERE type = 'P' AND name = 'SYNC_CreateMissingTables')
	DROP PROC [dbo].[SYNC_CreateMissingTables]
	GO
	--CREATE PROCEDURE
	SET ANSI_NULLS ON
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	/*
	Stored Procedure, to create missing tables at target database by compareing
	both source AND target databases.
	EXEC [dbo].[SYNC_CreateMissingTables] 'AdventureWork_StableVersion','AventureWork_Target'
	*/
	CREATE PROCEDURE [dbo].[SYNC_CreateMissingTables]
	(
	@SyncFromDB VARCHAR(200), --Source database name
	@SyncToDB VARCHAR(200) --Target database name
	)
	AS
	BEGIN
	CREATE TABLE #AlterStatements (TSQLStmt VARCHAR(MAX))
	EXEC
	( 'INSERT INTO #AlterStatements
	SELECT '' IF NOT EXISTS (SELECT 1 FROM '+@SyncToDB+'.SYS.TABLES WHERE NAME = '''''' + so.name + '''''')
	BEGIN create table '+@SyncToDB+'.dbo.['' + so.name + ''] ('' +
	REVERSE(CASE WHEN LEFT(LTRIM(REVERSE(o.list)),1)='',''THEN SUBSTRING(REVERSE(o.list),CHARINDEX('','',
	REVERSE(o.list),1)+1,LEN(REVERSE(o.list))) ELSE REVERSE(o.list) END) + '')''
	+ CASE WHEN tc.Constraint_Name IS NULL THEN '' END ''
	ELSE ''ALTER TABLE '+@SyncToDB+'.dbo.['' + so.Name + ''] ADD CONSTRAINT [''
	+ tc.Constraint_Name + ''] PRIMARY KEY '' + '' (''
	+ LEFT(j.List, Len(j.List) - 1) + '') END ''
	END
	FROM ' + @SyncFromDB
	+ '.dbo.sysobjects so
	CROSS APPLY ( SELECT '' ['' + column_name + ''] '' + data_type
	+ case data_type
	WHEN ''sql_variant'' THEN ''''
	WHEN ''text'' THEN ''''
	WHEN ''ntext'' THEN ''''
	WHEN ''image'' THEN ''''
	WHEN ''decimal''
	THEN ''(''
	+ CAST(numeric_precision as varchar)
	+ '', ''
	+ CAST(numeric_scale as varchar)
	+ '')''
	ELSE coalesce(''(''
	+ case WHEN character_maximum_length = -1
	THEN ''MAX''
	ELSE CAST(character_maximum_length as varchar)
	END + '')'', '''')
	END + '' ''
	+ case WHEN exists ( select id
	from '
	+ @SyncFromDB
	+ '.dbo.syscolumns
	WHERE object_name(id) = so.name
	AND name = column_name
	AND columnproperty(id, name, ''IsIdentity'') = 1 )
	THEN ''IDENTITY(''
	+ CAST(ident_seed(''[''+so.name+'']'') as varchar)
	+ '',''
	+ CAST(ident_incr(''[''+so.name+'']'') as varchar)
	+ '')''
	ELSE ''''
	END + '' ''
	+ ( case WHEN IS_NULLABLE = ''No'' THEN ''NOT '' ELSE '''' END ) + ''NULL ''
	+ case WHEN COLUMN_DEFAULT IS NOT NULL
	THEN ''DEFAULT ''
	+ COLUMN_DEFAULT
	ELSE ''''
	END + '', ''
	from ' + @SyncFromDB
	+ '.information_schema.columns
	WHERE table_name = so.name
	order by ordinal_position
	FOR
	XML PATH('''')
	) o ( list )
	LEFT JOIN ' + @SyncFromDB
	+ '.information_schema.table_constraints tc on tc.Table_name = so.Name
	AND tc.Constraint_Type = ''PRIMARY KEY''
	CROSS APPLY ( select ''['' + Column_Name + ''], ''
	FROM ' + @SyncFromDB
	+ '.information_schema.key_column_usage kcu
	WHERE kcu.Constraint_Name = tc.Constraint_Name
	ORDER BY ORDINAL_POSITION
	FOR
	XML PATH('''')
	) j ( list )
	WHERE so.xtype = ''U''
	AND EXISTS
	(
	SELECT *
	FROM ' + @SyncFromDB + '.Sys.tables MasterDB
	LEFT JOIN ' + @SyncToDB
	+ '.sys.tables TargetDB ON MasterDB.name=TargetDB.name
	WHERE TargetDB.name is null
	AND MasterDB.type_desc=''USER_TABLE''
	AND MasterDB.NAME=so.NAME AND so.id=MasterDB.[OBJECT_ID]
	)
	ORDER BY so.name '
	)
	----CURSOR TO EXECUTE/PRINT EACH STATEMENT SEPARATELY-----
	DECLARE MY_CURSOR CURSOR READ_ONLY
	FOR
	SELECT TSQLStmt FROM #AlterStatements
	OPEN My_Cursor
	DECLARE @SQLstmt VARCHAR(MAX)
	Fetch NEXT FROM MY_Cursor INTO @SQLstmt
	While (@@FETCH_STATUS <> -1)
	BEGIN
	EXEC (@SQLstmt)
	-- PRINT (@SQLstmt)
	FETCH NEXT FROM MY_CURSOR INTO @SQLstmt
	END
	CLOSE MY_CURSOR
	DEALLOCATE MY_CURSOR
	-----
	DROP TABLE #AlterStatements
	END

view raw Sync_CreateMissingTables.sql hosted with ❤ by GitHub