Sunday, October 16, 2011

Rename SQL user defined data type

Should we be using MsSQL user defined data types (UDDT)?

One advantage of using is that the same type can be standardized across similar fields across several tables. For example, we can have a UDDT defined for Email. However, the headache arrives when we need to alter the data type of the UDDT. There is no alter type yet to do the job.

What are the options to solving this problem? Do we really have to drop all objects (tables, stored procedures, etc) that use the UDDT, re-create the UDDT with the new type, and then re-create all tables, stored procedures, etc? That sounds quite a lot of work to do isn't it? What if we have existing records in tables and dropping tables sound way too risky.

Luckily, there is a workaround without dropping objects that use the UDDT (credit to a post on stackoverflow)

/* Create a new UDDT */
exec sp_addtype @typename='myUDT', @phystype='varchar(100)'
go

/* Create a table */
create table myTable (field1 myUdt)
go

/* Add a 'temporary' UDDT with the new definition */ 
exec sp_addtype @typename='tem_myUDT', @phystype='nvarchar(100)'
go

/*
1. Run below select statement
2. Copy the output result and run it
*/
select 'alter table dbo.' + TABLE_NAME + ' alter column ' + COLUMN_NAME + ' tem_myUDT' 
from INFORMATION_SCHEMA.COLUMNS 
where DOMAIN_NAME = 'myUDT'
go

/* Remove the old UDDT */ 
exec sp_droptype myUDT
go

/* Rename the 'temporary' UDDT to the correct name */ 
exec sp_rename 'tem_myUDT', 'myUDT', 'USERDATATYPE'
go

It works!

No comments:

Post a Comment

Do provide your constructive comment. I appreciate that.