Tuesday, March 31, 2009

How to alter a User Defined Data Type?

 

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

No comments:

Post a Comment