Skip to content

Instantly share code, notes, and snippets.

@lionofdezert
Created November 29, 2013 18:46
Show Gist options
  • Save lionofdezert/7710225 to your computer and use it in GitHub Desktop.
Save lionofdezert/7710225 to your computer and use it in GitHub Desktop.
Stored Procedure, to create missing columns at target database by comparing both source and target databases.
--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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment