views:

727

answers:

2

I created few user defined types in DB as below

CREATE TYPE [dbo].[StringID] FROM [nvarchar](20) NOT NULL

and assigned to various tables. My tables in db are in various schemas (not only dbo)

But I realized I need bigger field, and I need to alter, e.g increase from nvarchar to nvarchar, but there is no ALTER TYPE statement

I need a script that temp table/ cursor whatever and save there all tables and fields where my type is used. Then change existing fields to base type - e.g. from CustID [StringID] to CustID [nvarchar(20)]. Drop the user type and recreate it with new type - e.g. nvarchar(50) and finally set back fields to user type

I do not have rules define on types, so don't have to drop rules and re-add them

I'm not very familiar with T-Sql, so any help is appreciated.

+3  A: 

This is what I normally use, albeit a bit manual:

/* Add a 'temporary' UDDT with the new definition */ 
exec sp_addtype t_myudt_tmp, 'numeric(18,5)', NULL 


/* Build a command to alter all the existing columns - cut and 
** paste the output, then run it */ 
select 'alter table dbo.' + TABLE_NAME + 
       ' alter column ' + COLUMN_NAME + ' t_myudt_tmp' 
from INFORMATION_SCHEMA.COLUMNS 
where DOMAIN_NAME = 't_myudt' 

/* Remove the old UDDT */ 
exec sp_droptype t_mydut


/* Rename the 'temporary' UDDT to the correct name */ 
exec sp_rename 't_myudt_tmp', 't_myudt', 'USERDATATYPE'
Philip Fourie
That's a good start. I added TABLE_SCHEMA in select since my tables are not all in dbo schema, but in various other schemas.Now I need to figure how to select into a cursor or temp table, then execute a statement created a string.Thanks for help
bzamfir
That's probably the only viable way to go, since unfortunately, SQL Server does not have an "ALTER TYPE" command (why not?)
marc_s
You can vote for "ALTER TYPE" command on https://connect.microsoft.com/SQLServer/feedback/details/319134/msft-mso-support-alter-type
vasek7
A: 

Great idea to auto generate the script - but I believe this will NOT preserve the 'nullability' of the columns. They will all end up nullable.

Dave