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
- Backup your database
- These scripts are provided AS IS without warranty of any kind.
Script:
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:
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.
