USE DBName2
GO
SELECT 'USE [DBName2] '
UNION ALL
SELECT 'GO'
UNION ALL
SELECT '--A.IS_NULLABLE <> B.IS_NULLABLE '
UNION ALL
SELECT
DISTINCT 'IF 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+'] ALTER COLUMN ['+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)+') ' 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
+ ' GO'
FROM [DBName1].INFORMATION_SCHEMA.COLUMNS A
JOIN [DBName1].INFORMATION_SCHEMA.TABLES C ON A.TABLE_SCHEMA = C.TABLE_SCHEMA AND A.TABLE_NAME = C.TABLE_NAME
JOIN DBName2.INFORMATION_SCHEMA.COLUMNS B ON A.TABLE_SCHEMA = B.TABLE_SCHEMA COLLATE SQL_Latin1_General_CP1_CI_AS AND A.TABLE_NAME = B.TABLE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS AND A.COLUMN_NAME = B.COLUMN_NAME COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE A.IS_NULLABLE <> B.IS_NULLABLE COLLATE SQL_Latin1_General_CP1_CI_AS
AND TABLE_TYPE = 'BASE TABLE'
UNION ALL
SELECT 'GO'
UNION ALL
SELECT '--A.DATA_TYPE <> B.DATA_TYPE '
UNION ALL
SELECT
DISTINCT 'IF 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+'] ALTER COLUMN ['+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)+') ' 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
+ ' GO'
FROM [DBName1].INFORMATION_SCHEMA.COLUMNS A
JOIN [DBName1].INFORMATION_SCHEMA.TABLES C ON A.TABLE_SCHEMA = C.TABLE_SCHEMA AND A.TABLE_NAME = C.TABLE_NAME
JOIN DBName2.INFORMATION_SCHEMA.COLUMNS B ON A.TABLE_SCHEMA = C.TABLE_SCHEMA AND A.TABLE_NAME = B.TABLE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS AND A.COLUMN_NAME = B.COLUMN_NAME COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE 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 <> CASE WHEN B.CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN +'('+CASE WHEN CAST( B.CHARACTER_MAXIMUM_LENGTH AS VARCHAR)= -1 THEN 'MAX' ELSE CAST( B.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) END+')' ELSE '' END
AND TABLE_TYPE = 'BASE TABLE'
UNION ALL
SELECT 'GO'
UNION ALL
SELECT '--A.NUMERIC_PRECISION = B.NUMERIC_PRECISION AND A.NUMERIC_SCALE <> B.NUMERIC_SCALE'
UNION ALL
SELECT
DISTINCT 'IF 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+'] ALTER COLUMN ['+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
+ ' GO'
FROM [DBName1].INFORMATION_SCHEMA.COLUMNS A
JOIN [DBName1].INFORMATION_SCHEMA.TABLES C ON A.TABLE_SCHEMA = C.TABLE_SCHEMA AND A.TABLE_NAME = C.TABLE_NAME
JOIN DBName2.INFORMATION_SCHEMA.COLUMNS B ON A.TABLE_SCHEMA = C.TABLE_SCHEMA AND A.TABLE_NAME = B.TABLE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS AND A.COLUMN_NAME = B.COLUMN_NAME COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE A.NUMERIC_PRECISION = B.NUMERIC_PRECISION AND A.NUMERIC_SCALE <> B.NUMERIC_SCALE
AND TABLE_TYPE = 'BASE TABLE'
UNION ALL
SELECT 'GO'
UNION ALL
SELECT '--A.DATA_TYPE = B.DATA_TYPE AND (A.NUMERIC_PRECISION <> B.NUMERIC_PRECISION OR A.NUMERIC_SCALE <> B.NUMERIC_SCALE )'
UNION ALL
SELECT
DISTINCT 'IF 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+'] ALTER COLUMN ['+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
+ ' GO'
FROM [DBName1].INFORMATION_SCHEMA.COLUMNS A
JOIN [DBName1].INFORMATION_SCHEMA.TABLES C ON A.TABLE_SCHEMA = C.TABLE_SCHEMA AND A.TABLE_NAME = C.TABLE_NAME
JOIN DBName2.INFORMATION_SCHEMA.COLUMNS B ON A.TABLE_SCHEMA = C.TABLE_SCHEMA AND A.TABLE_NAME = B.TABLE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS AND A.COLUMN_NAME = B.COLUMN_NAME COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE A.DATA_TYPE = B.DATA_TYPE COLLATE SQL_Latin1_General_CP1_CI_AS AND (A.NUMERIC_PRECISION <> B.NUMERIC_PRECISION OR A.NUMERIC_SCALE <> B.NUMERIC_SCALE )
AND TABLE_TYPE = 'BASE TABLE'
UNION ALL
SELECT 'GO'
UNION ALL
SELECT 'GO'
UNION ALL
SELECT '--A.IS_NULLABLE <> B.IS_NULLABLE '
UNION ALL
SELECT
DISTINCT 'IF 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+'] ALTER COLUMN ['+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)+') ' 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
+ ' GO'
FROM [DBName1].INFORMATION_SCHEMA.COLUMNS A
JOIN [DBName1].INFORMATION_SCHEMA.TABLES C ON A.TABLE_SCHEMA = C.TABLE_SCHEMA AND A.TABLE_NAME = C.TABLE_NAME
JOIN DBName2.INFORMATION_SCHEMA.COLUMNS B ON A.TABLE_SCHEMA = B.TABLE_SCHEMA COLLATE SQL_Latin1_General_CP1_CI_AS AND A.TABLE_NAME = B.TABLE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS AND A.COLUMN_NAME = B.COLUMN_NAME COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE A.IS_NULLABLE <> B.IS_NULLABLE COLLATE SQL_Latin1_General_CP1_CI_AS
AND TABLE_TYPE = 'BASE TABLE'
UNION ALL
SELECT 'GO'
UNION ALL
SELECT '--A.DATA_TYPE <> B.DATA_TYPE '
UNION ALL
SELECT
DISTINCT 'IF 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+'] ALTER COLUMN ['+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)+') ' 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
+ ' GO'
FROM [DBName1].INFORMATION_SCHEMA.COLUMNS A
JOIN [DBName1].INFORMATION_SCHEMA.TABLES C ON A.TABLE_SCHEMA = C.TABLE_SCHEMA AND A.TABLE_NAME = C.TABLE_NAME
JOIN DBName2.INFORMATION_SCHEMA.COLUMNS B ON A.TABLE_SCHEMA = C.TABLE_SCHEMA AND A.TABLE_NAME = B.TABLE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS AND A.COLUMN_NAME = B.COLUMN_NAME COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE 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 <> CASE WHEN B.CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN +'('+CASE WHEN CAST( B.CHARACTER_MAXIMUM_LENGTH AS VARCHAR)= -1 THEN 'MAX' ELSE CAST( B.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) END+')' ELSE '' END
AND TABLE_TYPE = 'BASE TABLE'
UNION ALL
SELECT 'GO'
UNION ALL
SELECT '--A.NUMERIC_PRECISION = B.NUMERIC_PRECISION AND A.NUMERIC_SCALE <> B.NUMERIC_SCALE'
UNION ALL
SELECT
DISTINCT 'IF 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+'] ALTER COLUMN ['+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
+ ' GO'
FROM [DBName1].INFORMATION_SCHEMA.COLUMNS A
JOIN [DBName1].INFORMATION_SCHEMA.TABLES C ON A.TABLE_SCHEMA = C.TABLE_SCHEMA AND A.TABLE_NAME = C.TABLE_NAME
JOIN DBName2.INFORMATION_SCHEMA.COLUMNS B ON A.TABLE_SCHEMA = C.TABLE_SCHEMA AND A.TABLE_NAME = B.TABLE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS AND A.COLUMN_NAME = B.COLUMN_NAME COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE A.NUMERIC_PRECISION = B.NUMERIC_PRECISION AND A.NUMERIC_SCALE <> B.NUMERIC_SCALE
AND TABLE_TYPE = 'BASE TABLE'
UNION ALL
SELECT 'GO'
UNION ALL
SELECT '--A.DATA_TYPE = B.DATA_TYPE AND (A.NUMERIC_PRECISION <> B.NUMERIC_PRECISION OR A.NUMERIC_SCALE <> B.NUMERIC_SCALE )'
UNION ALL
SELECT
DISTINCT 'IF 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+'] ALTER COLUMN ['+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
+ ' GO'
FROM [DBName1].INFORMATION_SCHEMA.COLUMNS A
JOIN [DBName1].INFORMATION_SCHEMA.TABLES C ON A.TABLE_SCHEMA = C.TABLE_SCHEMA AND A.TABLE_NAME = C.TABLE_NAME
JOIN DBName2.INFORMATION_SCHEMA.COLUMNS B ON A.TABLE_SCHEMA = C.TABLE_SCHEMA AND A.TABLE_NAME = B.TABLE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS AND A.COLUMN_NAME = B.COLUMN_NAME COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE A.DATA_TYPE = B.DATA_TYPE COLLATE SQL_Latin1_General_CP1_CI_AS AND (A.NUMERIC_PRECISION <> B.NUMERIC_PRECISION OR A.NUMERIC_SCALE <> B.NUMERIC_SCALE )
AND TABLE_TYPE = 'BASE TABLE'
UNION ALL
SELECT 'GO'
No comments:
Post a Comment