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 + ')'
EXECUTE (@cmd)
END
if @PrintCommandsOnly = 'True' and @ScanTables = 'All'
BEGIN
SELECT * FROM #Temp
DROP TABLE #Temp
END
if @PrintCommandsOnly = 'False' and @ScanTables = 'All'
BEGIN
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
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',
@PrintCommandsOnly = 'FALSE'
|
