Monday, 1 July 2013

GENERIC 5. DataType Changes Generic Script2

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 NOT 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 A.IS_NULLABLE = 'NO' -- YES/NO
AND A.COLUMN_DEFAULT IS NOT 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(SCHEMA_ID)+']. ['+ T.name +'] DROP CONSTRAINT ['+I.name +']'
ELSE 'DROP INDEX ['+I.name+'] ON ['+SCHEMA_NAME(SCHEMA_ID)+'].['+ 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_SCHEMA AS TABLE_SCHEMA ,A.TABLE_NAME AS TABLE_NAME,A.COLUMN_NAME AS 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 AND A.TABLE_NAME = B.TABLE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS AND A.TABLE_SCHEMA = B.TABLE_SCHEMA COLLATE SQL_Latin1_General_CP1_CI_AS
AND A.TABLE_NAME NOT LIKE 'SYS%' AND IST.TABLE_TYPE = 'BASE TABLE'
AND A.IS_NULLABLE = 'NO' -- YES/NO
AND A.COLUMN_DEFAULT IS NOT NULL
AND object_name(t.object_id) NOT LIKE 'SYS%'
) IT ON 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 NOT NULL AND IS_NULLABLE IS NO'
UNION ALL
SELECT 'GO'
UNION ALL
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
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.IS_NULLABLE = 'NO' AND B.IS_NULLABLE = 'YES' THEN
'IF EXISTS (SELECT 1 FROM ['+A.TABLE_SCHEMA +'].['+A.TABLE_NAME +'] WHERE ['+A.COLUMN_NAME +'] IS NULL) UPDATE ['+A.TABLE_SCHEMA +'].['+A.TABLE_NAME +'] SET ['+A.COLUMN_NAME +'] = ISNULL(['+A.COLUMN_NAME +'],'++ CASE WHEN A.DATA_TYPE IN ('CHAR','VARCHAR','TEXT','NCHAR','NVARCHAR', 'NTEXT','XML') THEN '''''' ELSE '0' END+' ) '
ELSE '' END +
CASE WHEN A.DATA_TYPE = 'Date' AND B.DATA_TYPE LIKE '%VARCHAR%' THEN
'ALTER TABLE ['+A.TABLE_SCHEMA +'].['+A.TABLE_NAME +'] ALTER COLUMN ['+A.COLUMN_NAME +'] NVARCHAR(20) NULL UPDATE ['+B.TABLE_SCHEMA COLLATE SQL_Latin1_General_CP1_CI_AS+'].['+B.TABLE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS + '] 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 ['+B.TABLE_SCHEMA COLLATE SQL_Latin1_General_CP1_CI_AS+'].['+A.TABLE_NAME +'] ALTER COLUMN ['+A.COLUMN_NAME + '] NVARCHAR(100) NULL UPDATE ['+B.TABLE_SCHEMA COLLATE SQL_Latin1_General_CP1_CI_AS+'].['+B.TABLE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS + '] SET ' +B.COLUMN_NAME COLLATE SQL_Latin1_General_CP1_CI_AS + ' = CASE WHEN '+B.COLUMN_NAME COLLATE SQL_Latin1_General_CP1_CI_AS+' = '+'''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 AND A.TABLE_SCHEMA = C.TABLE_SCHEMA
JOIN [DBName2].INFORMATION_SCHEMA.COLUMNS B ON A.TABLE_NAME = B.TABLE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS AND A.TABLE_SCHEMA = B.TABLE_SCHEMA 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_SCHEMA = B.TABLE_SCHEMA COLLATE SQL_Latin1_General_CP1_CI_AS AND A.COLUMN_NAME = B.COLUMN_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 = 'NO' -- YES/NO
AND A.COLUMN_DEFAULT IS NOT NULL -- and a.DATA_TYPE ='nvarchar'
UNION ALL
SELECT 'GO'
--UNION ALL

No comments:

Post a comment