The only way to do it is to create a new User Define Data Type (UDDT), and change out all existing column to that UDDT, then you can drop the original one, and recreate it and change out the change you made previously. The problem is that you can drop the UDDT if it is in use.(PN: this is same with SQL Server 2008)
In case of the UDDT is in use you need to follow these steps.
1. Allocate different data type for the fields which are using UDDT. Rather than allocating arbitrary data type it is better to allocate data type of the UDDT, so that there won’t be any issue with the existing data.
2.Drop Stored Procedures which are using UDDT. You can drop them by using following T-SQL. However, you need to get the scripts of those SPs to create them later.
DECLARE @dropsps VARCHAR(8000)
SET @dropsps = ''
SELECT @dropsps = @dropsps + ',' +
OBJECT_NAME(id)
FROM sys.syscomments
WHERE TEXT LIKE '%latitude%'
AND OBJECT_NAME(id) LIKE 'usp%'
SET @dropsps = 'DROP PROC ' + RIGHT(@dropsps, LEN(@a) - 1)
EXEC ( @dropsps
)
3. Drop the UDDT
4. Create new UDDT
5. Allocate new UDDT to the fields which had previous UDDT.
6. Create Dropped Stored Procedures
You can use following script and you can change the first variable value according to your requirement. This script was tested for few data types.
SET NOCOUNT ON
DECLARE @udt VARCHAR(150)
DECLARE @udtschema VARCHAR(150)
DECLARE @newudtschema VARCHAR(150)
DECLARE @newudtDataType VARCHAR(150)
DECLARE @newudtDataSize smallint
DECLARE @OtherParameter VARCHAR(50)
SET @udt = 'Name' -- Existing UDDT
SET @udtschema = 'dbo' -- Schema of the UDDT
SET @newudtDataType = 'varchar' -- Data type for te new UDDT
SET @newudtDataSize = 500 -- Lenght of the new UDDT
SET @newudtschema = 'dbo' -- Schema of the new UDDT
SET @OtherParameter = ' NULL' -- Other parameters like NULL , NOT NULL
DECLARE @Datatype VARCHAR(50),
@Datasize SMALLINT
DECLARE @varcharDataType VARCHAR(50)
DECLARE @Schemaname VARCHAR(50),
@TableName VARCHAR(50),
@FiledName VARCHAR(50)
CREATE TABLE #udtflds
(
Schemaname VARCHAR(50),
TableName VARCHAR(50),
FiledName VARCHAR(50)
)
SELECT TOP 1
@Datatype = Data_type,
@Datasize = character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE Domain_name = @udt
SET @varcharDataType = @Datatype
IF @DataType Like '%char%'
AND @Datasize IS NOT NULL
AND ( @newudtDataType <> 'varchar(max)'
OR @newudtDataType <> 'nvarchar(max)'
)
BEGIN
SET @varcharDataType = @varcharDataType + '('
+ CAST(@Datasize AS VARCHAR(50)) + ')'
END
INSERT INTO #udtflds
SELECT TABLE_SCHEMA,
TABLE_NAME,
Column_Name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE Domain_name = @udt
DECLARE @exec VARCHAR(500)
DECLARE alter_cursor CURSOR
FOR SELECT Schemaname,
TableName,
FiledName
FROM #udtflds
OPEN alter_cursor
FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @exec = 'Alter Table ' + @Schemaname + '.' + @TableName
+ ' ALTER COLUMN ' + @FiledName + ' ' + @varcharDataType
EXECUTE ( @exec
)
FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName
END
CLOSE alter_cursor
SET @exec = 'DROP TYPE [' + @udtschema + '].[' + @udt + ']'
EXEC ( @exec
)
SET @varcharDataType = @newudtDataType
IF @newudtDataType Like '%char%'
AND @newudtDataSize IS NOT NULL
AND ( @newudtDataType <> 'varchar(max)'
OR @newudtDataType <> 'nvarchar(max)'
)
BEGIN
SET @varcharDataType = @varcharDataType + '('
+ CAST(@newudtDataSize AS VARCHAR(50)) + ')'
END
SET @exec = 'CREATE TYPE [' + @newudtschema + '].[' + @udt + '] FROM '
+ @varcharDataType + ' ' + @OtherParameter
EXEC ( @exec
)
OPEN alter_cursor
FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @exec = 'Alter Table ' + @Schemaname + '.' + @TableName
+ ' ALTER COLUMN ' + @FiledName + ' ' + '[' + @newudtschema
+ '].[' + @udt + ']'
EXECUTE ( @exec
)
FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName
END
CLOSE alter_cursor
DEALLOCATE alter_cursor
SELECT *
FROM #udtflds
DROP TABLE #udtflds
