Alter user defined type in Sql Server

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.


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' 
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' 

We are using the following procedure, it allows us to re-create a type from scratch, which is "a start". It renames the existing type, creates the type, recompiles stored procs and then drops the old type. This takes care of scenarios where simply dropping the old type-definition fails due to references to that type.

Usage Example:

exec RECREATE_TYPE @schema='dbo', @typ_nme='typ_foo', @sql='AS TABLE([bar] varchar(10) NOT NULL)'


    @schema     VARCHAR(100),       -- the schema name for the existing type
    @typ_nme    VARCHAR(128),       -- the type-name (without schema name)
    @sql        VARCHAR(MAX)        -- the SQL to create a type WITHOUT the "CREATE TYPE schema.typename" part
    @scid       BIGINT,
    @typ_id     BIGINT,
    @temp_nme   VARCHAR(1000),
    @msg        VARCHAR(200)
    -- find the existing type by schema and name
    SELECT @scid = [SCHEMA_ID] FROM sys.schemas WHERE UPPER(name) = UPPER(@schema);
    IF (@scid IS NULL) BEGIN
        SET @msg = 'Schema ''' + @schema + ''' not found.';
        RAISERROR (@msg, 1, 0);
    SELECT @typ_id = system_type_id FROM sys.types WHERE UPPER(name) = UPPER(@typ_nme);
    SET @temp_nme = @typ_nme + '_rcrt'; -- temporary name for the existing type

    -- if the type-to-be-recreated actually exists, then rename it (give it a temporary name)
    -- if it doesn't exist, then that's OK, too.
    IF (@typ_id IS NOT NULL) BEGIN
        exec sp_rename @objname=@typ_nme, @newname= @temp_nme, @objtype='USERDATATYPE'

    -- now create the new type
    SET @sql = 'CREATE TYPE ' + @schema + '.' + @typ_nme + ' ' + @sql;
    exec sp_sqlexec @sql;

    -- if we are RE-creating a type (as opposed to just creating a brand-spanking-new type)...
    IF (@typ_id IS NOT NULL) BEGIN
        exec recompile_prog;    -- then recompile all stored procs (that may have used the type)
        exec sp_droptype @typename=@temp_nme;   -- and drop the temporary type which is now no longer referenced


CREATE PROCEDURE [dbo].[recompile_prog]
    DECLARE @v TABLE (RecID INT IDENTITY(1,1), spname sysname)
    -- retrieve the list of stored procedures
        '[' + s.[name] + '].[' + + ']'     
        (SELECT, sp.schema_id, sp.is_ms_shipped FROM sys.procedures sp UNION SELECT, so.SCHEMA_ID, so.is_ms_shipped FROM sys.objects so WHERE so.type_desc LIKE '%FUNCTION%') items
        INNER JOIN sys.schemas s ON s.schema_id = items.schema_id    
        WHERE is_ms_shipped = 0;

    -- counter variables
    DECLARE @cnt INT, @Tot INT;
    SELECT @cnt = 1;
    SELECT @Tot = COUNT(*) FROM @v;
    DECLARE @spname sysname
    -- start the loop
    WHILE @Cnt <= @Tot BEGIN    
        SELECT @spname = spname        
        FROM @v        
        WHERE RecID = @Cnt;
        --PRINT 'refreshing...' + @spname    
        BEGIN TRY        -- refresh the stored procedure        
            EXEC sp_refreshsqlmodule @spname    
        END TRY    
        BEGIN CATCH        
            PRINT 'Validation failed for : ' + @spname + ', Error:' + ERROR_MESSAGE();
        END CATCH    
        SET @Cnt = @cnt + 1;


there's a good example of a more comprehensive script here

It's worth noting that this script will include views if you have any. I ran it and instead of exec'ing inline generated a script as the output which I then tweaked and ran.

Also, if you have functions/sprocs using the user defeined types you'll need to drop those before running your script.

Lesson Learned: in future, don't bother with UDTs they're more hassle than they're worth.


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)

      Schemaname VARCHAR(50),
      TableName VARCHAR(50),
      FiledName VARCHAR(50)

        @Datatype = Data_type,
        @Datasize = character_maximum_length
WHERE   Domain_name = @udt
        AND Domain_schema = @udtschema

SET @varcharDataType = @Datatype
IF @DataType Like '%char%'
    AND @Datasize IS NOT NULL
    AND ( @newudtDataType <> 'varchar(max)'
          OR @newudtDataType <> 'nvarchar(max)'
        SET @varcharDataType = @varcharDataType + '('
            + CAST(@Datasize AS VARCHAR(50)) + ')'

INSERT  INTO #udtflds
        WHERE   Domain_name = @udt
                AND Domain_schema = @udtschema


DECLARE alter_cursor CURSOR
    FOR SELECT  Schemaname,
        FROM    #udtflds

OPEN alter_cursor
FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName

        SET @exec = 'Alter Table ' + @Schemaname + '.' + @TableName
            + '  ALTER COLUMN ' + @FiledName + ' ' + @varcharDataType
        EXECUTE ( @exec
        FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName


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)'
        SET @varcharDataType = @varcharDataType + '('
            + CAST(@newudtDataSize AS VARCHAR(50)) + ')'

SET @exec = 'CREATE TYPE [' + @newudtschema + '].[' + @udt + '] FROM '
    + @varcharDataType + ' ' + @OtherParameter
EXEC ( @exec

OPEN alter_cursor
FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName

        SET @exec = 'Alter Table ' + @Schemaname + '.' + @TableName
            + '  ALTER COLUMN ' + @FiledName + ' ' + '[' + @newudtschema
            + '].[' + @udt + ']'
        EXECUTE ( @exec
        FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName

CLOSE alter_cursor
DEALLOCATE alter_cursor
FROM    #udtflds

DROP TABLE #udtflds

1: has replaced

As devio says there is no way to simply edit a UDT if it's in use.

A work-round through SMS that worked for me was to generate a create script and make the appropriate changes; rename the existing UDT; run the create script; recompile the related sprocs and drop the renamed version.

The solutions provided here can only be applied if the user defined types are used in table definitions only, and if the UDT columns are not indexed.

Some developers also have SP's and functions using UDT parameters, which is not covered either. (see comments on Robin's link and in the Connect entry)

The Connect entry from 2007 has finally been closed after 3 years:

Thank you for submitting this suggestion, but given its priority relative to the many other items in our queue, it is unlikely that we will actually complete it. As such, we are closing this suggestion as “won’t fix”.

I tried to solve a similiar problem ALTERing XML SCHEMA COLLECTIONS, and the steps seem to mostly apply to ALTER TYPE, too:

To drop a UDT, the following steps are necessary:

  • If a table column references the UDT, it has to be converted to the underlying type
  • If the table column has a default constraint, drop the default constraint
  • If a procedure or function has UDT parameters, the procedure or function has to be dropped
  • If there is an index on a UDT column, the index has to be dropped
  • If the index is a primary key, all foreign keys have to be dropped
  • If there are computed columns based on a UDT column, the computed columns have to be dropped
  • If there are indexes on these computed columns, the indexes have to be dropped
  • If there are schema-bound views, functions, or procedures based on tables containing UDT columns, these objects have to be dropped

New answer to an old question:

Visual Studio Database Projects handle the drop and recreate process when you deploy changes. It will drop stored procs that use UDDTs and then recreate them after dropping and recreating the data type.

The simplest way to do this is through Visual Studio's object explorer, which is also supported in the Community edition.

Once you have made a connection to SQL server, browse to the type, right click and select View Code, make your changes to the schema of the user defined type and click update. Visual Studio should show you all of the dependencies for that object and generate scripts to update the type and recompile dependencies.

1.Rename the old UDT, 2.Execute query , 3.Drop the old UDT.

Simple DROP TYPE first then CREATE TYPE again with corrections/alterations?

There is a simple test to see if it is defined before you drop it ... much like a table, proc or function -- if I wasn't at work I would look what that is?

(I only skimmed above too ... if I read it wrong I apologise in advance! ;)

Need Your Help

Automatic translation before serialization in symfony2 with JMSSerializerBundle

symfony doctrine jmsserializerbundle

I have some logic to apply after getting entities from database( by findAll() ) and before serializing the result to json.

CDI cannot retrieve servlet responde using @Context annotation

web-services java-ee dependency-injection jax-rs cdi

I have a jax-rs web service returning json, and I'd like to add cors headers to it.