Get Alter Script By Comparing 2 Databases

DROP PROC GETALTERSCRIPT_BYCOMPARING_2DB

GO

/*=============================================================

EXEC  GETALTERSCRIPT_BYCOMPARING_2DB 'OLD_DB','NEW_DB'
============================================================*/

CREATE PROC GETALTERSCRIPT_BYCOMPARING_2DB
(
@OLD VARCHAR(500),
@NEW VARCHAR(500)
)
AS
BEGIN

/*=====================================================================================
THIS PROC COMPARES BETWEEN THOSE TABLES WHICH EXISTS IN BOTH DATABASES
AND GENERATE ALTER SCRIPTS OF NEWLY ADDED COLUMNS..
*===================================================================================*/


SET NOCOUNT ON
DECLARE @QUERY VARCHAR(MAX)

        IF NOT EXISTS(SELECT 1 FROM SYS.DATABASES WHERE NAME= @OLD)
BEGIN
SELECT 'OLD DATABASE DOESN''T EXISTS.'
RETURN
END
        IF NOT EXISTS(SELECT 1 FROM SYS.DATABASES WHERE NAME= @NEW)
BEGIN
SELECT 'NEW DATABASE DOESN''T EXISTS.'
RETURN
END


SET @QUERY='SELECT  ''IF NOT EXISTS(SELECT TOP 1 1 '' + CHAR(10) +
''FROM INFORMATION_SCHEMA.COLUMNS '' + CHAR(10) +
''WHERE [TABLE_NAME] = '''''' + TABLE_NAME + '''''''' + CHAR(10) +
''AND [COLUMN_NAME] = ''''''+ COLUMN_NAME + '''''')'' + CHAR(10) +
''BEGIN '' + CHAR(10) + CHAR(9) +
''ALTER TABLE ''+ TABLE_NAME + CHAR(10) + CHAR(9) +
+ ''ADD '' + COLUMN_NAME +'' '' +  CASE DATA_TYPE WHEN ''BIGINT'' THEN ''BIGINT''
WHEN ''BINARY'' THEN ''BINARY('' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR)  + '')''
WHEN ''BIT'' THEN ''BIT''
WHEN ''CHAR'' THEN ''CHAR('' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR)  + '')''
WHEN ''DATETIME'' THEN ''DATETIME''
WHEN ''DECIMAL'' THEN ''DECIMAL('' + CAST(NUMERIC_PRECISION AS VARCHAR) + '','' + CAST(NUMERIC_SCALE AS VARCHAR)  + '')''
WHEN ''FLOAT'' THEN ''FLOAT''
WHEN ''IMAGE'' THEN ''IMAGE''
WHEN ''INT'' THEN ''INT''
WHEN ''MONEY'' THEN ''MONEY''
WHEN ''NCHAR'' THEN ''NCHAR('' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR)  + '')''
WHEN ''NTEXT'' THEN ''''
WHEN ''NUMERIC'' THEN ''NUMERIC('' + CAST(NUMERIC_PRECISION AS VARCHAR) + '','' + CAST(NUMERIC_SCALE AS VARCHAR)  + '')''
WHEN ''NVARCHAR'' THEN ''NVARCHAR''
WHEN ''REAL'' THEN ''REAL''
WHEN ''SMALLDATETIME'' THEN ''SMALLDATETIME''
WHEN ''SMALLINT'' THEN ''SMALLINT''
WHEN ''SMALLMONEY'' THEN ''SMALLMONEY''
WHEN ''SQL_VARIANT'' THEN ''SQL_VARIANT''
WHEN ''SYSNAME'' THEN ''SYSNAME''
WHEN ''TEXT'' THEN ''TEXT''
WHEN ''TIMESTAMP'' THEN ''TIMESTAMP''
WHEN ''TINYINT'' THEN ''TINYINT''
WHEN ''UNIQUEIDENTIFIER'' THEN ''UNIQUEIDENTIFIER''
WHEN ''VARBINARY'' THEN ''VARBINARY('' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR)  + '')''
WHEN ''VARCHAR'' THEN ''VARCHAR(''  + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR)  + '')''
END
+ CASE IS_NULLABLE WHEN ''YES'' THEN '' NULL''
   WHEN ''NO''  THEN '' NOT NULL''
ELSE ''NULL'' END
+ CASE ISNULL(COLUMN_DEFAULT,'''') WHEN '''' THEN ''''
  ELSE '' DEFAULT ''  + COLUMN_DEFAULT END  + CHAR(10) +
''END'' + CHAR(10) +
''ELSE'' + CHAR(10) + CHAR(9) +
''SELECT ''''COLUMN '' + COLUMN_NAME + '' ALREADY EXISTS IN '' + TABLE_NAME +'''''''' + CHAR(10)
FROM ' + @NEW + '.INFORMATION_SCHEMA.COLUMNS AS NEW
WHERE NOT EXISTS (SELECT ''X''  FROM ' + @OLD + '.INFORMATION_SCHEMA.COLUMNS AS OLD
  WHERE OLD.TABLE_NAME=NEW.TABLE_NAME
  AND OLD.COLUMN_NAME=NEW.COLUMN_NAME)
AND TABLE_NAME IN (SELECT DISTINCT TABLE_NAME
   FROM ' + @OLD + '.INFORMATION_SCHEMA.COLUMNS AS OLD)'

EXEC(@QUERY)

SET NOCOUNT OFF

END