SQL Server : Database Schema Sychronization Script for Columns

As per my commitment with my blog readers, here is one more script I use for missing columns creation during database schema synchronization process.

(Note: These synchronization scripts are just modified by me, BUT are property of SQL Server community. Thanks to Amna Asif and other basic writers of these scripts)

	--DROP IF ALREADY CREATED
	IF EXISTS (SELECT 1 FROM sys.objects WHERE type = 'P' AND name = 'SYNC_CreateMissingColumns')
	DROP PROC [dbo].[SYNC_CreateMissingColumns]
	GO
	--CREATE PROCEDURE
	SET ANSI_NULLS ON
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	/*
	Stored Procedure, to create missing columns at target database by comparing both source and target databases.
	EXEC [dbo].[SYNC_CreateMissingColumns] 'AdventureWork_StableVersion','AventureWork_Target'
	*/
	CREATE PROCEDURE [dbo].[SYNC_CreateMissingColumns]
	(
	@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
	CASE WHEN MM.COLTYPE = ''varchar'' or MM.COLTYPE = ''nvarchar'' or MM.COLTYPE = ''char'' or MM.COLTYPE = ''nchar''
	THEN ''ALTER TABLE [' + @SyncToDB
	+ '].[dbo].['' + MM.TableName + ''] ADD ['' + MM.name + ''] '' + MM.COLTYPE + ''('' + REPLACE(CAST(MM.max_length AS VARCHAR),-1,''MAX'') + '') ''
	+ case when exists ( SELECT 1
	FROM ' + @SyncFromDB + '.sys.columns C
	WHERE object_name(C.OBJECT_ID) = MM.tablename
	AND C.NAME=MM.name
	and Is_Identity = 1 )
	then '' IDENTITY(''
	+ cast(ident_seed(''[' + @SyncFromDB
	+ '].[dbo].[''+MM.tablename+'']'') as varchar)
	+ '',''
	+ cast(ident_incr(''[' + @SyncFromDB
	+ '].[dbo].[''+MM.tablename+'']'') as varchar)
	+ '')''
	else ''''
	end + '' ''
	+(SELECT ( case when IS_NULLABLE = ''No'' then '' NOT '' else '''' end ) + '' NULL '' + CASE WHEN ISC.COLUMN_DEFAULT IS NOT NULL OR ISC.COLUMN_DEFAULT<>''''
	THEN '' DEFAULT '' + ISC.COLUMN_DEFAULT
	ELSE '''' END
	FROM ' + @SyncFromDB + '.information_schema.columns ISC
	WHERE ISC.table_name = MM.tablename
	AND ISC.COLUMN_NAME=MM.NAME
	)+
	'' ''
	WHEN MM.COLTYPE = ''numeric'' or MM.COLTYPE = ''decimal''
	THEN ''ALTER TABLE [' + @SyncToDB
	+ '].[dbo].['' + MM.TableName + ''] ADD ['' + MM.name + ''] '' + MM.COLTYPE + ''('' + CAST(MM.Precision AS VARCHAR)+ '', '' + CAST(MM.Scale AS VARCHAR) + '') ''
	+ case when exists ( SELECT 1
	FROM ' + @SyncFromDB + '.sys.columns C
	WHERE object_name(C.OBJECT_ID) = MM.tablename
	AND C.NAME=MM.name
	and Is_Identity = 1 )
	then '' IDENTITY(''
	+ cast(ident_seed(''[' + @SyncFromDB
	+ '].[dbo].[''+MM.tablename+'']'') as varchar)
	+ '',''
	+ cast(ident_incr(''[' + @SyncFromDB
	+ '].[dbo].[''+MM.tablename+'']'') as varchar)
	+ '')''
	else ''''
	end + '' ''
	+(SELECT ( case when IS_NULLABLE = ''No'' then '' NOT '' else '''' end ) + '' NULL '' + CASE WHEN ISC.COLUMN_DEFAULT IS NOT NULL OR ISC.COLUMN_DEFAULT<>''''
	THEN '' DEFAULT '' + ISC.COLUMN_DEFAULT
	ELSE '''' END
	FROM ' + @SyncFromDB + '.information_schema.columns ISC
	WHERE ISC.table_name = MM.tablename
	AND ISC.COLUMN_NAME=MM.NAME
	)
	+ '' ''
	ELSE ''ALTER TABLE [' + @SyncToDB
	+ '].[dbo].['' + MM.TableName + ''] ADD ['' + MM.name+ ''] '' + MM.COLTYPE
	+ case when exists ( SELECT 1
	FROM ' + @SyncFromDB + '.sys.columns C
	WHERE object_name(C.OBJECT_ID) = MM.tablename
	AND C.NAME=MM.name
	and Is_Identity = 1 )
	then '' IDENTITY(''
	+ cast(ident_seed(''[' + @SyncFromDB
	+ '].[dbo].[''+MM.tablename+'']'') as varchar)
	+ '',''
	+ cast(ident_incr(''[' + @SyncFromDB
	+ '].[dbo].[''+MM.tablename+'']'') as varchar)
	+ '')''
	else ''''
	end + '' ''
	+(SELECT ( case when IS_NULLABLE = ''No'' then '' NOT '' else '''' end ) + '' NULL '' + CASE WHEN ISC.COLUMN_DEFAULT IS NOT NULL OR ISC.COLUMN_DEFAULT<>''''
	THEN '' DEFAULT '' + ISC.COLUMN_DEFAULT
	ELSE '''' END
	FROM ' + @SyncFromDB + '.information_schema.columns ISC
	WHERE ISC.table_name = MM.tablename
	AND ISC.COLUMN_NAME=MM.NAME
	)
	+ '' ''
	END
	FROM ( SELECT YY.Name as ColType,XX.column_id columnID, XX.*
	FROM ( SELECT AA.Name as TableName,BB.*
	FROM ' + @SyncFromDB + '.sys.tables AA,' + @SyncFromDB
	+ '.sys.columns BB
	WHERE AA.object_id = BB.object_id
	) XX,
	' + @SyncFromDB + '.sys.types YY
	WHERE XX.user_type_id = YY.user_type_id
	) MM
	left outer join ( SELECT YY.Name as ColType,XX.column_id columnID, XX.*
	FROM ( SELECT AA.Name as TableName, BB.*
	FROM ' + @SyncToDB + '.sys.tables AA,
	' + @SyncToDB + '.sys.columns BB
	WHERE AA.object_id = BB.object_id
	) XX,
	' + @SyncToDB + '.sys.types YY
	WHERE XX.user_type_id = YY.user_type_id
	) SS on MM.tablename = SS.tablename
	and MM.name = SS.name
	WHERE SS.name is null
	AND EXISTS
	(
	SELECT *
	FROM ' + @SyncFromDB + '.sys.tables MasterDB
	LEFT JOIN ' + @SyncToDB
	+ '.sys.tables SlaveDB ON MasterDB.name=SlaveDB.name
	WHERE SlaveDB.name is not null
	AND MasterDB.type_desc=''USER_TABLE''
	AND MasterDB.NAME=MM.TableName
	)
	order by MM.tablename,MM.columnID
	'
	)
	----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
	SELECT @SQLstmt = 'set NUMERIC_ROUNDABORT off
	set ANSI_NULLS,
	ANSI_PADDING,
	ANSI_WARNINGS,
	ARITHABORT,
	CONCAT_NULL_YIELDS_NULL,
	QUOTED_IDENTIFIER on ' + @SQLstmt
	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_CreateMissingColumns.sql hosted with ❤ by GitHub