T-SQL Query to change the datatype of multiple columns of single or multiple tables

T-SQL Query to change the datatype of multiple columns of single or multiple tables

There might be a situation where a person designed a database with a particular datatype for many tables and now you want to change the datatype to a different one for all those columns in a particular table or multiple tables due to various reasons.
Changing the datatype for a single table or five tables or 10 tables is a easy task, but when the tables list is in hundreds how easy is it do in the traditional way?
Below stored procedure gives you the flexibility of changing the datatype of multiple columns in a single or multiple tables at one go.

Things to note before running the scripts

1. Backup your database 
2. These scripts are provided AS IS without warranty of any kind.

Script:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81

CREATE PROC usp_ChangeColumnDatatype (@currentDataType nvarchar(25),    @DataTypeToSet nvarchar(50),    @ScanTables nvarchar(100),    @PrintCommandsOnly bit )   AS SET NOCOUNT ON DECLARE @ScanTables_Local nvarchar(100) SET @ScanTables_Local = '''' + REPLACE(REPLACE(@ScanTables,',',''','''),' ','') + '''' IF @ScanTables = 'All' BEGIN CREATE TABLE #Temp (CommandsToExecute nvarchar(max)) INSERT INTO #temp SELECT 'ALTER TABLE ' + OBJECT_NAME(o.object_id) +     ' ALTER COLUMN ' + c.name + ' ' + @DataTypeToSet +     CASE WHEN c.is_nullable = 0 THEN ' NOT NULL' ELSE ' NULL' END AS CommandsToExecute FROM sys.objects o INNER JOIN sys.columns c ON o.object_id=c.object_id INNER JOIN sys.types t ON c.system_type_id=t.system_type_id WHERE o.type='u' and t.name = @currentDataType END   IF @ScanTables <> 'All' BEGIN CREATE TABLE #Temp_SpecificTables (CommandsToExecute nvarchar(max))   DECLARE @Cmd nvarchar(max) SET @Cmd = 'INSERT INTO #Temp_SpecificTables  SELECT ''ALTER TABLE '' + OBJECT_NAME(o.object_id) +     '' ALTER COLUMN '' + c.name + ''' + @DataTypeToSet + ''' +     CASE WHEN c.is_nullable = 0 THEN ''NOT NULL'' ELSE ''NULL'' END AS CommandsToExecute FROM sys.objects o INNER JOIN sys.columns c ON o.object_id=c.object_id INNER JOIN sys.types t ON c.system_type_id=t.system_type_id WHERE o.type=''u'' and t.name = '''+@currentDataType+''' and OBJECT_NAME(o.object_id) in ('+ @ScanTables_Local + ')' --PRINT @cmd EXECUTE (@cmd) END if @PrintCommandsOnly = 'True' and @ScanTables = 'All' BEGIN SELECT * FROM #Temp DROP TABLE #Temp END   if @PrintCommandsOnly = 'False' and @ScanTables = 'All' BEGIN --SELECT * FROM #Temp PRINT 'Changing of the datatypes of table(s) '+ @ScanTables +' from ' + @currentDataType + ' to '+ @DataTypeToSet + ' started at ' + CAST(GETDATE() AS varchar) WHILE (SELECT COUNT(*) FROM #Temp) <> 0 BEGIN DECLARE @varTemp nvarchar(max) SELECT @varTemp = CommandsToExecute FROM #Temp EXECUTE (@varTemp) DELETE FROM #temp WHERE CommandsToExecute = @varTemp END DROP TABLE #Temp PRINT 'Changing of the datatypes of table(s) '+ @ScanTables +' from ' + @currentDataType + ' to '+ @DataTypeToSet + ' ended at ' + CAST(GETDATE() AS varchar) END   if @PrintCommandsOnly = 'True' and @ScanTables <> 'All' BEGIN SELECT * FROM #Temp_SpecificTables DROP TABLE #Temp_SpecificTables END   if @PrintCommandsOnly = 'False' and @ScanTables <> 'All' BEGIN --SELECT * FROM #Temp_SpecificTables PRINT 'Changing of the datatypes of table(s) '+ @ScanTables_Local +' from ' + @currentDataType + ' to '+ @DataTypeToSet + ' started at ' + CAST(GETDATE() AS varchar) WHILE (SELECT COUNT(*) FROM #Temp_SpecificTables) <> 0 BEGIN DECLARE @varTemp_SpecificTables nvarchar(max) SELECT @varTemp_SpecificTables = CommandsToExecute FROM #Temp_SpecificTables EXECUTE (@varTemp_SpecificTables) DELETE FROM #Temp_SpecificTables WHERE CommandsToExecute = @varTemp_SpecificTables END DROP TABLE #Temp_SpecificTables PRINT 'Changing of the datatypes of table(s) '+ @ScanTables_Local +' from ' + @currentDataType + ' to '+ @DataTypeToSet + ' ended at ' + CAST(GETDATE() AS varchar) END SET NOCOUNT OFF GO Usage: ? 1 2 3 4 EXEC usp_ChangeColumnDatatype @currentDataType = 'nvarchar',          @DataTypeToSet = 'varchar(50)',          @ScanTables = 'Table_1,Table_2', --Table1, Table2,Table3 or ALL          @PrintCommandsOnly = 'FALSE' -- TRUE - Will print the commands or FALSE - Will execute the commands.