Skip to main content

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!

Popular Posts

Ho Ching named 5th most powerful and is mistaken as first lady by Forbes

Forbes named Singapore Prime Minister wife and CEO of Temasek Holdings, Ho Ching, as the 5th most powerful woman in the world. Ho Ching is mistaken as Singapore's first lady! OMG!

I wonder how can Forbes makes such a blunder. For a complete list, refer to here.

How to stop FortiClient from starting automatically?

Installed FortiClient recently but the challenge in disabling the application/service from running automatically on every start-up annoyed me. Attempt to stop 'FortiClient Service Scheduler' only return 'Parameter is incorrect' error message.

An article on Technet help solve my trouble. To stop FortiClient from starting automatically, try the following:
Shut down FortiClient from the system tray.
Run net stop fortishield on command prompt.
Run msconfig.
On msconfig, switch to the Services tab. Clear the FortiClient Service Scheduler check box and click Apply.Run services.msc on command prompt to open up show all available services.Look for FortiClient Service Scheduler. Switch Startup type to Manual.Restart your computer. FortiClient should not be running automatically the next time round. Hope it helps.

Dr Lee Wei Ling - Why I choose to remain single

Why I choose to remain single

Sun, Apr 05, 2009 - The Straits Times
My parents have a loving relationship, but I knew I could not live my life around a husband
By Lee Wei Ling

My father became prime minister in 1959, when I was just four years old. Inevitably, most people know me as Lee Kuan Yew's daughter.

My every move, every word, is scrutinised and sometimes subject to criticism. One friend said I lived in a glass house. After my father's recent comment on my lack of culinary skills, another observed: 'You live in a house without any walls.' Fortunately, I am not easily embarrassed.

As long as my conscience is clear, what other people say of me does not bother me. Indeed, I am open about my life since the more I try to conceal from the public, the wilder the speculation becomes.

My father said of my mother two weeks ago: 'My wife was...not a traditional wife. She was educated, a professional woman... We had Ah Mahs, reliable, professional, dependable. (My wife) came b…