GENERIC 6. DataType Changes Generic Script3

USE DBName2

GO

SELECT 'USE [DBName2] '
UNION ALL
SELECT 'GO'
UNION ALL
SELECT '--DROP DEFAULT CONSTRAINTS IN EARLIER VERSION BEFORE ALTERING DATATYPE CHANGES'
UNION ALL
SELECT '--COLUMN_DEFAULT IS NULL FROM EARLIER VERSION'
UNION ALL
SELECT DISTINCT
'IF EXISTS (SELECT * FROM dbo.sysobjects WHERE NAME = N'''+SDC.name+''' AND type = ''D'') ALTER TABLE ['+A.TABLE_SCHEMA +'].['+B.TABLE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS+ '] DROP CONSTRAINT ['+ SDC.NAME + ']'+ ' GO'
FROM [DBName1].INFORMATION_SCHEMA.COLUMNS A
JOIN [DBName2].INFORMATION_SCHEMA.COLUMNS B ON 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
JOIN [DBName2].SYS.ALL_OBJECTS AO on AO.name COLLATE SQL_Latin1_General_CP1_CI_AS = A.TABLE_NAME
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
WHERE (A.DATA_TYPE <> B.DATA_TYPE COLLATE SQL_Latin1_General_CP1_CI_AS OR A.CHARACTER_MAXIMUM_LENGTH <> B.CHARACTER_MAXIMUM_LENGTH )
AND B.IS_NULLABLE = 'YES' -- YES/NO
AND B.COLUMN_DEFAULT IS NULL
UNION ALL
SELECT 'GO'
UNION ALL
SELECT '--DROP DEPENDENCY INDEXES'
UNION ALL
SELECT
DISTINCT
'IF EXISTS (SELECT * FROM sys.indexes WHERE OBJECT_NAME(OBJECT_ID) = N'''+T.name +''' AND name = N'''+I.name+''')'+
CASE WHEN I.is_primary_key = 1 OR I.is_unique_constraint =1 THEN 'ALTER TABLE ['+SCHEMA_NAME(T.SCHEMA_ID) +'].['+T.name + '] DROP CONSTRAINT ['+I.name +']'
ELSE 'DROP INDEX ['+I.name+'] ON ['+ T.name +'] WITH ( ONLINE = OFF )' END+ ' GO'
FROM [DBName2].sys.[tables] AS T
INNER JOIN [DBName2].sys.[indexes] I ON T.[object_id] = I.[object_id]
INNER JOIN [DBName2].sys.[index_columns] IC ON I.[object_id] = IC.[object_id] AND I.[index_id] = IC.[index_id]
INNER JOIN [DBName2].sys.[all_columns] AC ON T.[object_id] = AC.[object_id] AND IC.[column_id] = AC.[column_id]
INNER JOIN (
SELECT DISTINCT A.TABLE_NAME ,A.TABLE_SCHEMA ,A.COLUMN_NAME FROM [DBName2].sys.indexes i
JOIN [DBName2].sys.tables t ON i.object_id = t.object_id
JOIN [DBName2].sys.index_columns ic ON ic.object_id = t.object_id AND ic.index_id = i.index_id
JOIN [DBName2].sys.columns c ON c.object_id = t.object_id AND ic.column_id = c.column_id
JOIN [DBName1].INFORMATION_SCHEMA.COLUMNS A ON A.COLUMN_NAME = C.name COLLATE SQL_Latin1_General_CP1_CI_AS
JOIN [DBName1].INFORMATION_SCHEMA.TABLES IST ON object_name(t.object_id) COLLATE SQL_Latin1_General_CP1_CI_AS = IST.TABLE_NAME
JOIN [DBName2].INFORMATION_SCHEMA.COLUMNS B ON 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 OR A.CHARACTER_MAXIMUM_LENGTH <> B.CHARACTER_MAXIMUM_LENGTH ) AND A.TABLE_NAME = B.TABLE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS
AND A.TABLE_NAME NOT LIKE 'SYS%' AND IST.TABLE_TYPE = 'BASE TABLE'
AND A.IS_NULLABLE = 'YES' -- YES/NO
AND A.COLUMN_DEFAULT IS NULL
AND object_name(t.object_id) NOT LIKE 'SYS%'
) IT ON IT.TABLE_SCHEMA = SCHEMA_NAME(SCHEMA_ID) AND IT.TABLE_NAME = T.name COLLATE SQL_Latin1_General_CP1_CI_AS AND IT.TABLE_NAME = OBJECT_NAME(I.OBJECT_ID) COLLATE SQL_Latin1_General_CP1_CI_AS AND AC.name COLLATE SQL_Latin1_General_CP1_CI_AS = IT.COLUMN_NAME
UNION ALL
SELECT '--ALTER DATA TYPES IN EARLIER VERSION'
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+''''+CASE WHEN A.CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN ' AND CHARACTER_MAXIMUM_LENGTH = ' + CAST(A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR)+'' ELSE '' END+') BEGIN '+
CASE WHEN A.DATA_TYPE = 'Date' AND B.DATA_TYPE COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%VARCHAR%' THEN
'ALTER TABLE ['+A.TABLE_SCHEMA +'].['+A.TABLE_NAME +'] ALTER COLUMN ['+A.COLUMN_NAME+'] NVARCHAR(20) NULL UPDATE ['+A.TABLE_SCHEMA +'].['+A.TABLE_NAME +'] SET ' +B.COLUMN_NAME COLLATE SQL_Latin1_General_CP1_CI_AS + ' = CAST(CONVERT(DATE,RIGHT(' + B.COLUMN_NAME COLLATE SQL_Latin1_General_CP1_CI_AS +',4)+LEFT('+ B.COLUMN_NAME COLLATE SQL_Latin1_General_CP1_CI_AS +',2)+SUBSTRING(CAST('+B.COLUMN_NAME COLLATE SQL_Latin1_General_CP1_CI_AS +'  AS VARCHAR),3,2),112) AS VARCHAR) '
WHEN A.DATA_TYPE = 'datetimeoffset' AND B.DATA_TYPE COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%VARCHAR%' THEN
'ALTER TABLE ['+A.TABLE_SCHEMA +'].['+A.TABLE_NAME +'] ALTER COLUMN ['+A.COLUMN_NAME+'] NVARCHAR(100) NULL UPDATE ['+A.TABLE_SCHEMA +'].['+A.TABLE_NAME +'] SET ' +B.COLUMN_NAME + ' = CASE WHEN '+B.COLUMN_NAME+' = '+'''00000000''' +' THEN '+'''1900-01-01 00:00:00.0000000 +00:00'''+' ELSE CAST(CONVERT(DATE,RIGHT(' + B.COLUMN_NAME COLLATE SQL_Latin1_General_CP1_CI_AS +',4)+LEFT('+ B.COLUMN_NAME COLLATE SQL_Latin1_General_CP1_CI_AS +',2)+CAST(SUBSTRING('+B.COLUMN_NAME COLLATE SQL_Latin1_General_CP1_CI_AS +',3,2) AS VARCHAR) ,112) AS VARCHAR) +'+''' 00:00:00.0000000 +00:00'' END '
ELSE '' END+
'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 + ' 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 [DBName1].INFORMATION_SCHEMA.TABLES C ON A.TABLE_NAME = C.TABLE_NAME
JOIN [DBName2].INFORMATION_SCHEMA.COLUMNS B ON 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 OR A.CHARACTER_MAXIMUM_LENGTH <> B.CHARACTER_MAXIMUM_LENGTH ) AND A.TABLE_NAME = B.TABLE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS
AND A.TABLE_NAME NOT LIKE 'SYS%' AND C.TABLE_TYPE = 'BASE TABLE'
AND A.IS_NULLABLE = 'YES' -- YES/NO
AND A.COLUMN_DEFAULT IS NULL -- and a.DATA_TYPE ='nvarchar'
/*
SELECT
DISTINCT 'IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '''+A.TABLE_NAME+''' AND COLUMN_NAME = '''+A.COLUMN_NAME+''' AND DATA_TYPE = '''+A.DATA_TYPE+''') ALTER TABLE [dbo].['+A.TABLE_NAME+'] ALTER COLUMN ['+A.COLUMN_NAME+'] ['+ A.DATA_TYPE+ '] ' + CASE WHEN A.DATA_TYPE IN ('NTEXT','XML') THEN '' 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'
--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 [DBName1].INFORMATION_SCHEMA.TABLES C ON A.TABLE_NAME = C.TABLE_NAME
JOIN [DBName2].INFORMATION_SCHEMA.COLUMNS B ON A.TABLE_NAME = B.TABLE_NAME AND A.COLUMN_NAME = B.COLUMN_NAME
WHERE A.DATA_TYPE <> B.DATA_TYPE AND A.TABLE_NAME = B.TABLE_NAME
AND A.TABLE_NAME NOT LIKE 'SYS%' AND C.TABLE_TYPE = 'BASE TABLE'
AND A.IS_NULLABLE = 'YES' -- YES/NO
AND A.COLUMN_DEFAULT IS NULL
*/
UNION ALL
SELECT 'GO'
--UNION ALL