UDT / SQL 2000

[loriek]

In SQL 2000 is it possible to alter a UDT, such as varchar (10) to make it varchar (20)?

 

[gmmastros]

Yes. But it won't be fun. There are several steps to follow, so you'll need to be careful, and make sure you have a good backup of your database first.

Basically, you can add user defined data types, and you can drop user defined data types. But, you cannot alter a user defined data type. What makes this more difficult is that you cannot drop a user defined data type if it is being used.

So, the steps you need to follow are....

1. Get a list of columns for your UDT.
2. Alter the table so that it uses the basis of the UDT (varchar(10) in this case).
3. drop the UDT.
4. create a new UDT with the same name, but with varchar(20).
5. alter the tables so that the columns that were originally using the UDT (and subsequently changed to varchar) are changed back to using the UDT.

I know this looks ugly, and if you have large tables, this will run slow, so if you have an active database with many users and large tables, you should probably run this when the DB usage is low.

Here is a script I threw together.

[COLOR=green]-- Create a table variable to store the ALTER commands
[/color]
[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color](Id [COLOR=blue]Integer[/color] [COLOR=blue]Identity[/color](1,1), ConvertToVarChar [COLOR=blue]varchar[/color](1000), ConvertToUDT [COLOR=blue]varchar[/color](1000))

[COLOR=green]-- Get the ALTER commands
[/color][COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp(ConvertToVarChar, ConvertToUDT)
[COLOR=blue]Select[/color] [COLOR=red]'Alter Table ['[/color] + Table_Name + [COLOR=red]'] Alter Column ['[/color] + Column_Name + [COLOR=red]'] varchar(10)'[/color],
       [COLOR=red]'Alter Table ['[/color] + Table_Name + [COLOR=red]'] Alter Column ['[/color] + Column_Name + [COLOR=red]'] telephone'[/color]
[COLOR=blue]From[/color]   Information_Schema.Columns
[COLOR=blue]Where[/color]  Domain_Name = [COLOR=red]'telephone'[/color]


[COLOR=green]-- Loop through the table variable and execute the ConvertToVarChar command
[/color][COLOR=blue]Declare[/color] @i [COLOR=blue]Int[/color]
[COLOR=blue]Declare[/color] @Max [COLOR=blue]Int[/color]
[COLOR=blue]Declare[/color] @Command [COLOR=blue]VarChar[/color](1000)

[COLOR=blue]Select[/color] @i = 1, @Max = [COLOR=#FF00FF]Max[/color](Id) [COLOR=blue]From[/color] @Temp

[COLOR=blue]While[/color] @i <= @Max
  [COLOR=blue]Begin[/color]
    [COLOR=blue]Select[/color] @Command = ConvertToVarChar
    [COLOR=blue]From[/color]   @Temp
    [COLOR=blue]Where[/color]  Id = @i

    [COLOR=blue]Exec[/color] (@Command)

    [COLOR=blue]Set[/color] @i = @i + 1
  [COLOR=blue]End[/color]

[COLOR=green]-- Drop the UDT
[/color][COLOR=blue]exec[/color] sp_droptype [COLOR=red]'telephone'[/color]

[COLOR=green]-- Create the UDT with new string length
[/color][COLOR=blue]exec[/color] sp_addtype [COLOR=red]'telephone'[/color], [COLOR=red]'varchar(20)'[/color], [COLOR=red]'Not Null'[/color]

[COLOR=green]-- Loop through the table again to re-set the data type to UDT
[/color][COLOR=blue]Select[/color] @i = 1, @Max = [COLOR=#FF00FF]Max[/color](Id) [COLOR=blue]From[/color] @Temp

[COLOR=blue]While[/color] @i <= @Max
  [COLOR=blue]Begin[/color]
    [COLOR=blue]Select[/color] @Command = ConvertToUDT
    [COLOR=blue]From[/color]   @Temp
    [COLOR=blue]Where[/color]  Id = @i

    [COLOR=blue]Exec[/color] (@Command)

    [COLOR=blue]Set[/color] @i = @i + 1
  [COLOR=blue]End[/color]

I STRONGLY encourage you to run this on a backup copy of your database before you run it on a production database.

Also, notice that I hard coded some info. This script alters a udt named telephone. So, you will need to change this to your UDT's actual name. Also, this is hard coded to change from varchar(10) to varchar(20) and it makes the column NOT NULL, which may not be appropriate for you.

Basically... feel free to use this script as a [!]basis[/!] for writing your own. Think of this as a template, and nothing else.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLSister]

This is of course why most of us don't use UDTs. Does anybody know if this has changed in 2005?

"NOTHING is more important in a database than integrity." ESquared

 

[gmmastros]

After a quick look at the 2005 documentation, it appears as though there is no built-in way to change a UDT type.

I should also point out that SQL 2005 has CREATE TYPE and DROP TYPE commands, and encourages you to NOT use sp_addtype and sp_droptype. Those commands still exist, but are being deprecated in a future version.

Also, please note that the script I provide above will NOT work if you have created constraints for the UDT.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom