USE [DBName1]
GO
SELECT '--DROP DEFAULT CONSTRAINTS IN EARLIER VERSION BEFORE CREATE ADDING COLUMNS OR DEFAULT CONSTRAINTS FROM LATER VERSION'
UNION ALL
SELECT
DISTINCT
'IF EXISTS (SELECT * FROM dbo.sysobjects WHERE PARENT_OBJ = OBJECT_ID(N''['+S.name +'].'+T.name+''') AND NAME = '''+SDC.name +''' AND type = ''D'') ALTER TABLE ['+S.name +'].['+ T.NAME + '] DROP CONSTRAINT ['+ SDC.NAME + ']'+ ' GO'
FROM [DBName2].SYS.TABLES T
JOIN [DBName2].SYS.schemas S ON T.schema_id = S.schema_id
JOIN [DBName2].SYS.ALL_OBJECTS AO on AO.name = T.name AND S.schema_id = AO.schema_id
join [DBName2].SYS.ALL_COLUMNS AC on AO.object_id = AC.object_id
JOIN [DBName2].SYS.DEFAULT_CONSTRAINTS SDC ON SDC.parent_OBJECT_ID = AC.object_id AND SDC.parent_column_id = AC.column_id
UNION ALL
SELECT 'GO'
UNION ALL
SELECT '--Create script to identify Newly Added Columns (added as nullable or not nullable fields) in the existing tabels '
UNION ALL
SELECT '--COLUMN_DEFAULT IS NOT NULL AND IS_NULLABLE IS NO '
UNION ALL
SELECT
DISTINCT 'IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '''+A.TABLE_SCHEMA+''' AND TABLE_NAME = '''+A.TABLE_NAME+''' AND COLUMN_NAME = '''+A.COLUMN_NAME+''' AND DATA_TYPE = '''+A.DATA_TYPE+''') ALTER TABLE ['+A.TABLE_SCHEMA+'].['+A.TABLE_NAME+'] ADD ['+A.COLUMN_NAME+'] ['+ A.DATA_TYPE+ '] ' + CASE WHEN A.DATA_TYPE IN ('NTEXT','XML') THEN '' WHEN A.DATA_TYPE = 'datetimeoffset' THEN '('+ CAST(A.DATETIME_PRECISION AS VARCHAR)+') ' WHEN A.DATA_TYPE = 'decimal' THEN ' ('+ CAST(A.NUMERIC_PRECISION AS VARCHAR)+','+CAST(A.NUMERIC_SCALE AS VARCHAR) +')' ELSE CASE WHEN A.CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN +'('+CASE WHEN CAST( A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR)= -1 THEN 'MAX' ELSE CAST( A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) END+')' ELSE '' END END ++ CASE WHEN A.COLUMN_DEFAULT IS NOT NULL THEN 'CONSTRAINT ['+SDC.name+'] DEFAULT(' +A.COLUMN_DEFAULT + ')' ELSE '' END+ CASE WHEN A.IS_NULLABLE = 'YES' THEN 'NULL' WHEN A.IS_NULLABLE = 'NO' THEN 'NOT NULL' END + ' '
+CASE WHEN COLUMNPROPERTY(OBJECT_ID(QUOTENAME(A.TABLE_SCHEMA)+'.'+QUOTENAME(A.TABLE_NAME)), A.COLUMN_NAME,'ISIDENTITY') = 1 THEN ' IDENTITY('+CAST(IDENT_SEED(QUOTENAME(A.TABLE_SCHEMA)+'.'+QUOTENAME(A.TABLE_NAME)) AS VARCHAR)+', '+ CAST(IDENT_INCR(QUOTENAME(A.TABLE_SCHEMA)+'.'+QUOTENAME(A.TABLE_NAME)) AS VARCHAR) + ')' ELSE '' END + ' GO'
--DISTINCT A.TABLE_NAME --,A.COLUMN_NAME,A.DATA_TYPE,A.IS_NULLABLE,A.COLUMN_DEFAULT,A.CHARACTER_MAXIMUM_LENGTH
FROM [DBName1].INFORMATION_SCHEMA.COLUMNS A
JOIN [DBName2].INFORMATION_SCHEMA.TABLES C ON A.TABLE_NAME = C.TABLE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS
JOIN [DBName1].SYS.default_constraints SDC ON OBJECT_NAME(PARENT_OBJECT_ID) = A.TABLE_NAME AND SCHEMA_NAME(SCHEMA_ID) = A.TABLE_SCHEMA
JOIN [DBName1].SYS.columns SC ON SDC.parent_column_id = SC.column_id AND SDC.PARENT_OBJECT_ID = SC.object_id AND SC.name = A.COLUMN_NAME
WHERE A.COLUMN_NAME NOT IN
(SELECT COLUMN_NAME COLLATE SQL_Latin1_General_CP1_CI_AS FROM [DBName2].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS = A.TABLE_NAME AND TABLE_SCHEMA COLLATE SQL_Latin1_General_CP1_CI_AS = A.TABLE_SCHEMA )
AND A.TABLE_NAME NOT LIKE 'SYS%' AND C.TABLE_TYPE COLLATE SQL_Latin1_General_CP1_CI_AS = 'BASE TABLE'
AND A.IS_NULLABLE = 'NO' -- YES/NO
AND A.COLUMN_DEFAULT IS NOT NULL --NULL/NOT NULL
UNION ALL
SELECT 'GO'
UNION ALL
SELECT '--COLUMN_DEFAULT IS NOT NULL AND IS_NULLABLE IS YES '
UNION ALL
SELECT
DISTINCT 'IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '''+A.TABLE_SCHEMA+''' AND TABLE_NAME = '''+A.TABLE_NAME+''' AND COLUMN_NAME = '''+A.COLUMN_NAME+''' AND DATA_TYPE = '''+A.DATA_TYPE+''') ALTER TABLE ['+A.TABLE_SCHEMA+'].['+A.TABLE_NAME+'] ADD ['+A.COLUMN_NAME+'] ['+ A.DATA_TYPE+ '] ' + CASE WHEN A.DATA_TYPE IN ('NTEXT','XML') THEN '' WHEN A.DATA_TYPE = 'datetimeoffset' THEN '('+ CAST(A.DATETIME_PRECISION AS VARCHAR)+') ' WHEN A.DATA_TYPE = 'decimal' THEN ' ('+ CAST(A.NUMERIC_PRECISION AS VARCHAR)+','+CAST(A.NUMERIC_SCALE AS VARCHAR) +')' ELSE CASE WHEN A.CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN +'('+CASE WHEN CAST( A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR)= -1 THEN 'MAX' ELSE CAST( A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) END+')' ELSE '' END END ++ CASE WHEN A.COLUMN_DEFAULT IS NOT NULL THEN 'CONSTRAINT ['+SDC.name+'] DEFAULT(' +A.COLUMN_DEFAULT + ')' ELSE '' END+ CASE WHEN A.IS_NULLABLE = 'YES' THEN 'NULL' WHEN A.IS_NULLABLE = 'NO' THEN 'NOT NULL' END + ' '
+CASE WHEN COLUMNPROPERTY(OBJECT_ID(QUOTENAME(A.TABLE_SCHEMA)+'.'+QUOTENAME(A.TABLE_NAME)), A.COLUMN_NAME,'ISIDENTITY') = 1 THEN ' IDENTITY('+CAST(IDENT_SEED(QUOTENAME(A.TABLE_SCHEMA)+'.'+QUOTENAME(A.TABLE_NAME)) AS VARCHAR)+', '+ CAST(IDENT_INCR(QUOTENAME(A.TABLE_SCHEMA)+'.'+QUOTENAME(A.TABLE_NAME)) AS VARCHAR) + ')' ELSE '' END + ' GO'
--DISTINCT A.TABLE_NAME --,A.COLUMN_NAME,A.DATA_TYPE,A.IS_NULLABLE,A.COLUMN_DEFAULT,A.CHARACTER_MAXIMUM_LENGTH
FROM [DBName1].INFORMATION_SCHEMA.COLUMNS A
JOIN [DBName2].INFORMATION_SCHEMA.TABLES C ON A.TABLE_NAME = C.TABLE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS AND A.TABLE_SCHEMA = C.TABLE_SCHEMA COLLATE SQL_Latin1_General_CP1_CI_AS
JOIN [DBName1].SYS.default_constraints SDC ON OBJECT_NAME(PARENT_OBJECT_ID) = A.TABLE_NAME AND SCHEMA_NAME(SCHEMA_ID) = A.TABLE_SCHEMA
JOIN [DBName1].SYS.columns SC ON SDC.parent_column_id = SC.column_id AND SDC.PARENT_OBJECT_ID = SC.object_id AND SC.name = A.COLUMN_NAME
WHERE A.COLUMN_NAME NOT IN
(SELECT COLUMN_NAME COLLATE SQL_Latin1_General_CP1_CI_AS FROM [DBName2].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS = A.TABLE_NAME AND TABLE_SCHEMA COLLATE SQL_Latin1_General_CP1_CI_AS = A.TABLE_SCHEMA )
AND A.TABLE_NAME NOT LIKE 'SYS%' AND C.TABLE_TYPE COLLATE SQL_Latin1_General_CP1_CI_AS = 'BASE TABLE'
AND A.IS_NULLABLE = 'YES' -- YES/NO
AND A.COLUMN_DEFAULT IS NOT NULL --NULL/NOT NULL
UNION ALL
SELECT 'GO'
UNION ALL
SELECT '--COLUMN_DEFAULT IS NULL AND IS_NULLABLE IS YES '
UNION ALL
SELECT
DISTINCT 'IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '''+A.TABLE_SCHEMA+''' AND TABLE_NAME = '''+A.TABLE_NAME+''' AND COLUMN_NAME = '''+A.COLUMN_NAME+''' AND DATA_TYPE = '''+A.DATA_TYPE+''') ALTER TABLE ['+A.TABLE_SCHEMA+'].['+A.TABLE_NAME+'] ADD ['+A.COLUMN_NAME+'] ['+ A.DATA_TYPE+ '] ' + CASE WHEN A.DATA_TYPE IN ('NTEXT','XML') THEN '' WHEN A.DATA_TYPE = 'datetimeoffset' THEN '('+ CAST(A.DATETIME_PRECISION AS VARCHAR)+') ' WHEN A.DATA_TYPE = 'decimal' THEN ' ('+ CAST(A.NUMERIC_PRECISION AS VARCHAR)+','+CAST(A.NUMERIC_SCALE AS VARCHAR) +')' ELSE CASE WHEN A.CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN +'('+CASE WHEN CAST( A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR)= -1 THEN 'MAX' ELSE CAST( A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) END+')' ELSE '' END END + CASE WHEN A.IS_NULLABLE = 'YES' THEN 'NULL' WHEN A.IS_NULLABLE = 'NO' THEN 'NOT NULL' END + ' ' + CASE WHEN A.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT(' +A.COLUMN_DEFAULT + ')' ELSE '' END
+CASE WHEN COLUMNPROPERTY(OBJECT_ID(QUOTENAME(A.TABLE_SCHEMA)+'.'+QUOTENAME(A.TABLE_NAME)), A.COLUMN_NAME,'ISIDENTITY') = 1 THEN ' IDENTITY('+CAST(IDENT_SEED(QUOTENAME(A.TABLE_SCHEMA)+'.'+QUOTENAME(A.TABLE_NAME)) AS VARCHAR)+', '+ CAST(IDENT_INCR(QUOTENAME(A.TABLE_SCHEMA)+'.'+QUOTENAME(A.TABLE_NAME)) AS VARCHAR) + ')' ELSE '' END + ' GO'
--DISTINCT A.TABLE_NAME --,A.COLUMN_NAME,A.DATA_TYPE,A.IS_NULLABLE,A.COLUMN_DEFAULT,A.CHARACTER_MAXIMUM_LENGTH
FROM [DBName1].INFORMATION_SCHEMA.COLUMNS A
JOIN [DBName2].INFORMATION_SCHEMA.TABLES C ON A.TABLE_NAME = C.TABLE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS AND A.TABLE_SCHEMA = C.TABLE_SCHEMA COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE A.COLUMN_NAME NOT IN
(SELECT COLUMN_NAME COLLATE SQL_Latin1_General_CP1_CI_AS FROM [DBName2].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS = A.TABLE_NAME AND TABLE_SCHEMA COLLATE SQL_Latin1_General_CP1_CI_AS = A.TABLE_SCHEMA )
AND A.TABLE_NAME NOT LIKE 'SYS%' AND C.TABLE_TYPE COLLATE SQL_Latin1_General_CP1_CI_AS = 'BASE TABLE'
AND A.IS_NULLABLE = 'YES' -- YES/NO
AND A.COLUMN_DEFAULT IS NULL --NULL/NOT NULL
UNION ALL
SELECT 'GO'
UNION ALL
SELECT '--COLUMN_DEFAULT IS NULL AND IS_NULLABLE IS NO AND IDENTITY PROPERTY IS TRUE'
UNION ALL
SELECT
DISTINCT 'IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '''+A.TABLE_SCHEMA+''' AND TABLE_NAME = '''+A.TABLE_NAME+''' AND COLUMN_NAME = '''+A.COLUMN_NAME+''' AND DATA_TYPE = '''+A.DATA_TYPE+''') ALTER TABLE ['+A.TABLE_SCHEMA+'].['+A.TABLE_NAME+'] ADD ['+A.COLUMN_NAME+'] ['+ A.DATA_TYPE+ '] ' + CASE WHEN A.DATA_TYPE IN ('NTEXT','XML') THEN '' WHEN A.DATA_TYPE = 'datetimeoffset' THEN '('+ CAST(A.DATETIME_PRECISION AS VARCHAR)+') ' WHEN A.DATA_TYPE = 'decimal' THEN ' ('+ CAST(A.NUMERIC_PRECISION AS VARCHAR)+','+CAST(A.NUMERIC_SCALE AS VARCHAR) +')' ELSE CASE WHEN A.CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN +'('+CASE WHEN CAST( A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR)= -1 THEN 'MAX' ELSE CAST( A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) END+')' ELSE '' END END + CASE WHEN A.IS_NULLABLE = 'YES' THEN 'NULL' WHEN A.IS_NULLABLE = 'NO' THEN 'NOT NULL' END + ' ' + CASE WHEN A.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT(' +A.COLUMN_DEFAULT + ')' ELSE '' END
+CASE WHEN COLUMNPROPERTY(OBJECT_ID(QUOTENAME(A.TABLE_SCHEMA)+'.'+QUOTENAME(A.TABLE_NAME)), A.COLUMN_NAME,'ISIDENTITY') = 1 THEN ' IDENTITY('+CAST(IDENT_SEED(QUOTENAME(A.TABLE_SCHEMA)+'.'+QUOTENAME(A.TABLE_NAME)) AS VARCHAR)+', '+ CAST(IDENT_INCR(QUOTENAME(A.TABLE_SCHEMA)+'.'+QUOTENAME(A.TABLE_NAME)) AS VARCHAR) + ')' ELSE '' END + ' GO'
--DISTINCT A.TABLE_NAME --,A.COLUMN_NAME,A.DATA_TYPE,A.IS_NULLABLE,A.COLUMN_DEFAULT,A.CHARACTER_MAXIMUM_LENGTH
FROM [DBName1].INFORMATION_SCHEMA.COLUMNS A
JOIN [DBName2].INFORMATION_SCHEMA.TABLES C ON A.TABLE_NAME = C.TABLE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS AND A.TABLE_SCHEMA = C.TABLE_SCHEMA COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE A.COLUMN_NAME NOT IN
(SELECT COLUMN_NAME COLLATE SQL_Latin1_General_CP1_CI_AS FROM [DBName2].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS = A.TABLE_NAME AND TABLE_SCHEMA COLLATE SQL_Latin1_General_CP1_CI_AS = A.TABLE_SCHEMA )
AND A.TABLE_NAME NOT LIKE 'SYS%' AND C.TABLE_TYPE COLLATE SQL_Latin1_General_CP1_CI_AS = 'BASE TABLE'
AND A.IS_NULLABLE = 'NO' -- YES/NO
AND A.COLUMN_DEFAULT IS NULL --NULL/NOT NULL
AND COLUMNPROPERTY(OBJECT_ID(QUOTENAME(A.TABLE_SCHEMA)+'.'+QUOTENAME(A.TABLE_NAME)), A.COLUMN_NAME,'ISIDENTITY') = 1
UNION ALL
SELECT 'GO'
/*
UNION ALL
SELECT '--COLUMN_DEFAULT IS NULL AND IS_NULLABLE IS NO'
UNION ALL
SELECT
DISTINCT 'IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '''+A.TABLE_SCHEMA+''' AND TABLE_NAME = '''+A.TABLE_NAME+''' AND COLUMN_NAME = '''+A.COLUMN_NAME+''' AND DATA_TYPE = '''+A.DATA_TYPE+''') ALTER TABLE [dbo].['+A.TABLE_NAME+'] ADD ['+A.COLUMN_NAME+'] ['+ A.DATA_TYPE+ '] ' + CASE WHEN A.DATA_TYPE IN ('NTEXT','XML') THEN '' WHEN A.DATA_TYPE = 'datetimeoffset' THEN '('+ CAST(A.DATETIME_PRECISION AS VARCHAR)+') ' WHEN A.DATA_TYPE = 'decimal' THEN ' ('+ CAST(A.NUMERIC_PRECISION AS VARCHAR)+','+CAST(A.NUMERIC_SCALE AS VARCHAR) +')' ELSE CASE WHEN A.CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN +'('+CASE WHEN CAST( A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR)= -1 THEN 'MAX' ELSE CAST( A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) END+')' ELSE '' END END + CASE WHEN A.IS_NULLABLE = 'YES' THEN 'NULL' WHEN A.IS_NULLABLE = 'NO' THEN 'NOT NULL' END + ' ' + CASE WHEN A.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT(' +A.COLUMN_DEFAULT + ')' ELSE '' END
+CASE WHEN COLUMNPROPERTY(OBJECT_ID(QUOTENAME(A.TABLE_SCHEMA)+'.'+QUOTENAME(A.TABLE_NAME)), A.COLUMN_NAME,'ISIDENTITY') = 1 THEN ' IDENTITY('+CAST(IDENT_SEED(A.TABLE_NAME) AS VARCHAR)+', '+ CAST(IDENT_INCR(A.TABLE_NAME) AS VARCHAR) + ')' ELSE '' END
--DISTINCT A.TABLE_NAME --,A.COLUMN_NAME,A.DATA_TYPE,A.IS_NULLABLE,A.COLUMN_DEFAULT,A.CHARACTER_MAXIMUM_LENGTH
FROM [DBName1].INFORMATION_SCHEMA.COLUMNS A
JOIN [DBName2].INFORMATION_SCHEMA.TABLES C ON A.TABLE_NAME = C.TABLE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE A.COLUMN_NAME NOT IN
(SELECT COLUMN_NAME COLLATE SQL_Latin1_General_CP1_CI_AS FROM [DBName2].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS = A.TABLE_NAME )
AND A.TABLE_NAME NOT LIKE 'SYS%' AND C.TABLE_TYPE COLLATE SQL_Latin1_General_CP1_CI_AS = 'BASE TABLE'
AND A.IS_NULLABLE = 'NO' -- YES/NO
AND A.COLUMN_DEFAULT IS NULL --NULL/NOT NULL
AND COLUMNPROPERTY(OBJECT_ID(QUOTENAME(A.TABLE_SCHEMA)+'.'+QUOTENAME(A.TABLE_NAME)), A.COLUMN_NAME,'ISIDENTITY') <> 1
GO*/
UNION ALL
SELECT
DISTINCT
'IF EXISTS (SELECT * FROM dbo.sysobjects WHERE PARENT_OBJ = OBJECT_ID(N''['+S.name +'].'+T.name+''') AND NAME = '''+SDC.name +''' AND type = ''D'') ALTER TABLE ['+S.name +'].['+ T.NAME + '] DROP CONSTRAINT ['+ SDC.NAME + ']'+ ' GO'
FROM [DBName2].SYS.TABLES T
JOIN [DBName2].SYS.schemas S ON T.schema_id = S.schema_id
JOIN [DBName2].SYS.ALL_OBJECTS AO on AO.name = T.name AND S.schema_id = AO.schema_id
join [DBName2].SYS.ALL_COLUMNS AC on AO.object_id = AC.object_id
JOIN [DBName2].SYS.DEFAULT_CONSTRAINTS SDC ON SDC.parent_OBJECT_ID = AC.object_id AND SDC.parent_column_id = AC.column_id
UNION ALL
SELECT 'GO'
UNION ALL
SELECT '--Create script to identify Newly Added Columns (added as nullable or not nullable fields) in the existing tabels '
UNION ALL
SELECT '--COLUMN_DEFAULT IS NOT NULL AND IS_NULLABLE IS NO '
UNION ALL
SELECT
DISTINCT 'IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '''+A.TABLE_SCHEMA+''' AND TABLE_NAME = '''+A.TABLE_NAME+''' AND COLUMN_NAME = '''+A.COLUMN_NAME+''' AND DATA_TYPE = '''+A.DATA_TYPE+''') ALTER TABLE ['+A.TABLE_SCHEMA+'].['+A.TABLE_NAME+'] ADD ['+A.COLUMN_NAME+'] ['+ A.DATA_TYPE+ '] ' + CASE WHEN A.DATA_TYPE IN ('NTEXT','XML') THEN '' WHEN A.DATA_TYPE = 'datetimeoffset' THEN '('+ CAST(A.DATETIME_PRECISION AS VARCHAR)+') ' WHEN A.DATA_TYPE = 'decimal' THEN ' ('+ CAST(A.NUMERIC_PRECISION AS VARCHAR)+','+CAST(A.NUMERIC_SCALE AS VARCHAR) +')' ELSE CASE WHEN A.CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN +'('+CASE WHEN CAST( A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR)= -1 THEN 'MAX' ELSE CAST( A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) END+')' ELSE '' END END ++ CASE WHEN A.COLUMN_DEFAULT IS NOT NULL THEN 'CONSTRAINT ['+SDC.name+'] DEFAULT(' +A.COLUMN_DEFAULT + ')' ELSE '' END+ CASE WHEN A.IS_NULLABLE = 'YES' THEN 'NULL' WHEN A.IS_NULLABLE = 'NO' THEN 'NOT NULL' END + ' '
+CASE WHEN COLUMNPROPERTY(OBJECT_ID(QUOTENAME(A.TABLE_SCHEMA)+'.'+QUOTENAME(A.TABLE_NAME)), A.COLUMN_NAME,'ISIDENTITY') = 1 THEN ' IDENTITY('+CAST(IDENT_SEED(QUOTENAME(A.TABLE_SCHEMA)+'.'+QUOTENAME(A.TABLE_NAME)) AS VARCHAR)+', '+ CAST(IDENT_INCR(QUOTENAME(A.TABLE_SCHEMA)+'.'+QUOTENAME(A.TABLE_NAME)) AS VARCHAR) + ')' ELSE '' END + ' GO'
--DISTINCT A.TABLE_NAME --,A.COLUMN_NAME,A.DATA_TYPE,A.IS_NULLABLE,A.COLUMN_DEFAULT,A.CHARACTER_MAXIMUM_LENGTH
FROM [DBName1].INFORMATION_SCHEMA.COLUMNS A
JOIN [DBName2].INFORMATION_SCHEMA.TABLES C ON A.TABLE_NAME = C.TABLE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS
JOIN [DBName1].SYS.default_constraints SDC ON OBJECT_NAME(PARENT_OBJECT_ID) = A.TABLE_NAME AND SCHEMA_NAME(SCHEMA_ID) = A.TABLE_SCHEMA
JOIN [DBName1].SYS.columns SC ON SDC.parent_column_id = SC.column_id AND SDC.PARENT_OBJECT_ID = SC.object_id AND SC.name = A.COLUMN_NAME
WHERE A.COLUMN_NAME NOT IN
(SELECT COLUMN_NAME COLLATE SQL_Latin1_General_CP1_CI_AS FROM [DBName2].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS = A.TABLE_NAME AND TABLE_SCHEMA COLLATE SQL_Latin1_General_CP1_CI_AS = A.TABLE_SCHEMA )
AND A.TABLE_NAME NOT LIKE 'SYS%' AND C.TABLE_TYPE COLLATE SQL_Latin1_General_CP1_CI_AS = 'BASE TABLE'
AND A.IS_NULLABLE = 'NO' -- YES/NO
AND A.COLUMN_DEFAULT IS NOT NULL --NULL/NOT NULL
UNION ALL
SELECT 'GO'
UNION ALL
SELECT '--COLUMN_DEFAULT IS NOT NULL AND IS_NULLABLE IS YES '
UNION ALL
SELECT
DISTINCT 'IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '''+A.TABLE_SCHEMA+''' AND TABLE_NAME = '''+A.TABLE_NAME+''' AND COLUMN_NAME = '''+A.COLUMN_NAME+''' AND DATA_TYPE = '''+A.DATA_TYPE+''') ALTER TABLE ['+A.TABLE_SCHEMA+'].['+A.TABLE_NAME+'] ADD ['+A.COLUMN_NAME+'] ['+ A.DATA_TYPE+ '] ' + CASE WHEN A.DATA_TYPE IN ('NTEXT','XML') THEN '' WHEN A.DATA_TYPE = 'datetimeoffset' THEN '('+ CAST(A.DATETIME_PRECISION AS VARCHAR)+') ' WHEN A.DATA_TYPE = 'decimal' THEN ' ('+ CAST(A.NUMERIC_PRECISION AS VARCHAR)+','+CAST(A.NUMERIC_SCALE AS VARCHAR) +')' ELSE CASE WHEN A.CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN +'('+CASE WHEN CAST( A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR)= -1 THEN 'MAX' ELSE CAST( A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) END+')' ELSE '' END END ++ CASE WHEN A.COLUMN_DEFAULT IS NOT NULL THEN 'CONSTRAINT ['+SDC.name+'] DEFAULT(' +A.COLUMN_DEFAULT + ')' ELSE '' END+ CASE WHEN A.IS_NULLABLE = 'YES' THEN 'NULL' WHEN A.IS_NULLABLE = 'NO' THEN 'NOT NULL' END + ' '
+CASE WHEN COLUMNPROPERTY(OBJECT_ID(QUOTENAME(A.TABLE_SCHEMA)+'.'+QUOTENAME(A.TABLE_NAME)), A.COLUMN_NAME,'ISIDENTITY') = 1 THEN ' IDENTITY('+CAST(IDENT_SEED(QUOTENAME(A.TABLE_SCHEMA)+'.'+QUOTENAME(A.TABLE_NAME)) AS VARCHAR)+', '+ CAST(IDENT_INCR(QUOTENAME(A.TABLE_SCHEMA)+'.'+QUOTENAME(A.TABLE_NAME)) AS VARCHAR) + ')' ELSE '' END + ' GO'
--DISTINCT A.TABLE_NAME --,A.COLUMN_NAME,A.DATA_TYPE,A.IS_NULLABLE,A.COLUMN_DEFAULT,A.CHARACTER_MAXIMUM_LENGTH
FROM [DBName1].INFORMATION_SCHEMA.COLUMNS A
JOIN [DBName2].INFORMATION_SCHEMA.TABLES C ON A.TABLE_NAME = C.TABLE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS AND A.TABLE_SCHEMA = C.TABLE_SCHEMA COLLATE SQL_Latin1_General_CP1_CI_AS
JOIN [DBName1].SYS.default_constraints SDC ON OBJECT_NAME(PARENT_OBJECT_ID) = A.TABLE_NAME AND SCHEMA_NAME(SCHEMA_ID) = A.TABLE_SCHEMA
JOIN [DBName1].SYS.columns SC ON SDC.parent_column_id = SC.column_id AND SDC.PARENT_OBJECT_ID = SC.object_id AND SC.name = A.COLUMN_NAME
WHERE A.COLUMN_NAME NOT IN
(SELECT COLUMN_NAME COLLATE SQL_Latin1_General_CP1_CI_AS FROM [DBName2].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS = A.TABLE_NAME AND TABLE_SCHEMA COLLATE SQL_Latin1_General_CP1_CI_AS = A.TABLE_SCHEMA )
AND A.TABLE_NAME NOT LIKE 'SYS%' AND C.TABLE_TYPE COLLATE SQL_Latin1_General_CP1_CI_AS = 'BASE TABLE'
AND A.IS_NULLABLE = 'YES' -- YES/NO
AND A.COLUMN_DEFAULT IS NOT NULL --NULL/NOT NULL
UNION ALL
SELECT 'GO'
UNION ALL
SELECT '--COLUMN_DEFAULT IS NULL AND IS_NULLABLE IS YES '
UNION ALL
SELECT
DISTINCT 'IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '''+A.TABLE_SCHEMA+''' AND TABLE_NAME = '''+A.TABLE_NAME+''' AND COLUMN_NAME = '''+A.COLUMN_NAME+''' AND DATA_TYPE = '''+A.DATA_TYPE+''') ALTER TABLE ['+A.TABLE_SCHEMA+'].['+A.TABLE_NAME+'] ADD ['+A.COLUMN_NAME+'] ['+ A.DATA_TYPE+ '] ' + CASE WHEN A.DATA_TYPE IN ('NTEXT','XML') THEN '' WHEN A.DATA_TYPE = 'datetimeoffset' THEN '('+ CAST(A.DATETIME_PRECISION AS VARCHAR)+') ' WHEN A.DATA_TYPE = 'decimal' THEN ' ('+ CAST(A.NUMERIC_PRECISION AS VARCHAR)+','+CAST(A.NUMERIC_SCALE AS VARCHAR) +')' ELSE CASE WHEN A.CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN +'('+CASE WHEN CAST( A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR)= -1 THEN 'MAX' ELSE CAST( A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) END+')' ELSE '' END END + CASE WHEN A.IS_NULLABLE = 'YES' THEN 'NULL' WHEN A.IS_NULLABLE = 'NO' THEN 'NOT NULL' END + ' ' + CASE WHEN A.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT(' +A.COLUMN_DEFAULT + ')' ELSE '' END
+CASE WHEN COLUMNPROPERTY(OBJECT_ID(QUOTENAME(A.TABLE_SCHEMA)+'.'+QUOTENAME(A.TABLE_NAME)), A.COLUMN_NAME,'ISIDENTITY') = 1 THEN ' IDENTITY('+CAST(IDENT_SEED(QUOTENAME(A.TABLE_SCHEMA)+'.'+QUOTENAME(A.TABLE_NAME)) AS VARCHAR)+', '+ CAST(IDENT_INCR(QUOTENAME(A.TABLE_SCHEMA)+'.'+QUOTENAME(A.TABLE_NAME)) AS VARCHAR) + ')' ELSE '' END + ' GO'
--DISTINCT A.TABLE_NAME --,A.COLUMN_NAME,A.DATA_TYPE,A.IS_NULLABLE,A.COLUMN_DEFAULT,A.CHARACTER_MAXIMUM_LENGTH
FROM [DBName1].INFORMATION_SCHEMA.COLUMNS A
JOIN [DBName2].INFORMATION_SCHEMA.TABLES C ON A.TABLE_NAME = C.TABLE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS AND A.TABLE_SCHEMA = C.TABLE_SCHEMA COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE A.COLUMN_NAME NOT IN
(SELECT COLUMN_NAME COLLATE SQL_Latin1_General_CP1_CI_AS FROM [DBName2].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS = A.TABLE_NAME AND TABLE_SCHEMA COLLATE SQL_Latin1_General_CP1_CI_AS = A.TABLE_SCHEMA )
AND A.TABLE_NAME NOT LIKE 'SYS%' AND C.TABLE_TYPE COLLATE SQL_Latin1_General_CP1_CI_AS = 'BASE TABLE'
AND A.IS_NULLABLE = 'YES' -- YES/NO
AND A.COLUMN_DEFAULT IS NULL --NULL/NOT NULL
UNION ALL
SELECT 'GO'
UNION ALL
SELECT '--COLUMN_DEFAULT IS NULL AND IS_NULLABLE IS NO AND IDENTITY PROPERTY IS TRUE'
UNION ALL
SELECT
DISTINCT 'IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '''+A.TABLE_SCHEMA+''' AND TABLE_NAME = '''+A.TABLE_NAME+''' AND COLUMN_NAME = '''+A.COLUMN_NAME+''' AND DATA_TYPE = '''+A.DATA_TYPE+''') ALTER TABLE ['+A.TABLE_SCHEMA+'].['+A.TABLE_NAME+'] ADD ['+A.COLUMN_NAME+'] ['+ A.DATA_TYPE+ '] ' + CASE WHEN A.DATA_TYPE IN ('NTEXT','XML') THEN '' WHEN A.DATA_TYPE = 'datetimeoffset' THEN '('+ CAST(A.DATETIME_PRECISION AS VARCHAR)+') ' WHEN A.DATA_TYPE = 'decimal' THEN ' ('+ CAST(A.NUMERIC_PRECISION AS VARCHAR)+','+CAST(A.NUMERIC_SCALE AS VARCHAR) +')' ELSE CASE WHEN A.CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN +'('+CASE WHEN CAST( A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR)= -1 THEN 'MAX' ELSE CAST( A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) END+')' ELSE '' END END + CASE WHEN A.IS_NULLABLE = 'YES' THEN 'NULL' WHEN A.IS_NULLABLE = 'NO' THEN 'NOT NULL' END + ' ' + CASE WHEN A.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT(' +A.COLUMN_DEFAULT + ')' ELSE '' END
+CASE WHEN COLUMNPROPERTY(OBJECT_ID(QUOTENAME(A.TABLE_SCHEMA)+'.'+QUOTENAME(A.TABLE_NAME)), A.COLUMN_NAME,'ISIDENTITY') = 1 THEN ' IDENTITY('+CAST(IDENT_SEED(QUOTENAME(A.TABLE_SCHEMA)+'.'+QUOTENAME(A.TABLE_NAME)) AS VARCHAR)+', '+ CAST(IDENT_INCR(QUOTENAME(A.TABLE_SCHEMA)+'.'+QUOTENAME(A.TABLE_NAME)) AS VARCHAR) + ')' ELSE '' END + ' GO'
--DISTINCT A.TABLE_NAME --,A.COLUMN_NAME,A.DATA_TYPE,A.IS_NULLABLE,A.COLUMN_DEFAULT,A.CHARACTER_MAXIMUM_LENGTH
FROM [DBName1].INFORMATION_SCHEMA.COLUMNS A
JOIN [DBName2].INFORMATION_SCHEMA.TABLES C ON A.TABLE_NAME = C.TABLE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS AND A.TABLE_SCHEMA = C.TABLE_SCHEMA COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE A.COLUMN_NAME NOT IN
(SELECT COLUMN_NAME COLLATE SQL_Latin1_General_CP1_CI_AS FROM [DBName2].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS = A.TABLE_NAME AND TABLE_SCHEMA COLLATE SQL_Latin1_General_CP1_CI_AS = A.TABLE_SCHEMA )
AND A.TABLE_NAME NOT LIKE 'SYS%' AND C.TABLE_TYPE COLLATE SQL_Latin1_General_CP1_CI_AS = 'BASE TABLE'
AND A.IS_NULLABLE = 'NO' -- YES/NO
AND A.COLUMN_DEFAULT IS NULL --NULL/NOT NULL
AND COLUMNPROPERTY(OBJECT_ID(QUOTENAME(A.TABLE_SCHEMA)+'.'+QUOTENAME(A.TABLE_NAME)), A.COLUMN_NAME,'ISIDENTITY') = 1
UNION ALL
SELECT 'GO'
/*
UNION ALL
SELECT '--COLUMN_DEFAULT IS NULL AND IS_NULLABLE IS NO'
UNION ALL
SELECT
DISTINCT 'IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '''+A.TABLE_SCHEMA+''' AND TABLE_NAME = '''+A.TABLE_NAME+''' AND COLUMN_NAME = '''+A.COLUMN_NAME+''' AND DATA_TYPE = '''+A.DATA_TYPE+''') ALTER TABLE [dbo].['+A.TABLE_NAME+'] ADD ['+A.COLUMN_NAME+'] ['+ A.DATA_TYPE+ '] ' + CASE WHEN A.DATA_TYPE IN ('NTEXT','XML') THEN '' WHEN A.DATA_TYPE = 'datetimeoffset' THEN '('+ CAST(A.DATETIME_PRECISION AS VARCHAR)+') ' WHEN A.DATA_TYPE = 'decimal' THEN ' ('+ CAST(A.NUMERIC_PRECISION AS VARCHAR)+','+CAST(A.NUMERIC_SCALE AS VARCHAR) +')' ELSE CASE WHEN A.CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN +'('+CASE WHEN CAST( A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR)= -1 THEN 'MAX' ELSE CAST( A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) END+')' ELSE '' END END + CASE WHEN A.IS_NULLABLE = 'YES' THEN 'NULL' WHEN A.IS_NULLABLE = 'NO' THEN 'NOT NULL' END + ' ' + CASE WHEN A.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT(' +A.COLUMN_DEFAULT + ')' ELSE '' END
+CASE WHEN COLUMNPROPERTY(OBJECT_ID(QUOTENAME(A.TABLE_SCHEMA)+'.'+QUOTENAME(A.TABLE_NAME)), A.COLUMN_NAME,'ISIDENTITY') = 1 THEN ' IDENTITY('+CAST(IDENT_SEED(A.TABLE_NAME) AS VARCHAR)+', '+ CAST(IDENT_INCR(A.TABLE_NAME) AS VARCHAR) + ')' ELSE '' END
--DISTINCT A.TABLE_NAME --,A.COLUMN_NAME,A.DATA_TYPE,A.IS_NULLABLE,A.COLUMN_DEFAULT,A.CHARACTER_MAXIMUM_LENGTH
FROM [DBName1].INFORMATION_SCHEMA.COLUMNS A
JOIN [DBName2].INFORMATION_SCHEMA.TABLES C ON A.TABLE_NAME = C.TABLE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE A.COLUMN_NAME NOT IN
(SELECT COLUMN_NAME COLLATE SQL_Latin1_General_CP1_CI_AS FROM [DBName2].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS = A.TABLE_NAME )
AND A.TABLE_NAME NOT LIKE 'SYS%' AND C.TABLE_TYPE COLLATE SQL_Latin1_General_CP1_CI_AS = 'BASE TABLE'
AND A.IS_NULLABLE = 'NO' -- YES/NO
AND A.COLUMN_DEFAULT IS NULL --NULL/NOT NULL
AND COLUMNPROPERTY(OBJECT_ID(QUOTENAME(A.TABLE_SCHEMA)+'.'+QUOTENAME(A.TABLE_NAME)), A.COLUMN_NAME,'ISIDENTITY') <> 1
GO*/
No comments:
Post a Comment