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