Alter user defined type in SQL Server

Sql Problem Overview

I created few user defined types in my database as below

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

and assigned them to various tables. The tables in my database are in various schemas (not only dbo)

But I realized I need bigger field, and I need to alter, e.g increase from [nvarchar](20) to [nvarchar](50), but there is no ALTER TYPE statement.

I need a script that uses a temp table/cursor whatever and saves all the 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 defined on types, so don't have to drop rules and re-add them.

Any help is appreciated.

Sql Solutions

Solution 1 - Sql

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'

Solution 2 - Sql

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

Code:

CREATE PROCEDURE [dbo].[RECREATE_TYPE]
	@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
AS DECLARE
	@scid		BIGINT,
	@typ_id		BIGINT,
	@temp_nme	VARCHAR(1000),
	@msg		VARCHAR(200)
BEGIN
	-- 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);
	END;
	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'
	END;	
	
	-- 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
	END;	
END

GO


CREATE PROCEDURE [dbo].[recompile_prog]
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @v TABLE (RecID INT IDENTITY(1,1), spname sysname)
	-- retrieve the list of stored procedures
	INSERT INTO 
		@v(spname) 
	SELECT 
		'[' + s.[name] + '].[' + items.name + ']'     
	FROM 
		(SELECT sp.name, sp.schema_id, sp.is_ms_shipped FROM sys.procedures sp UNION SELECT so.name, 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;
	END;

END

Solution 3 - Sql

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.

SET NOCOUNT ON

DECLARE @udt VARCHAR(150)
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)

CREATE TABLE #udtflds
    (
      Schemaname VARCHAR(50),
      TableName VARCHAR(50),
      FiledName VARCHAR(50)
    )

SELECT TOP 1
        @Datatype = Data_type,
        @Datasize = character_maximum_length
FROM    INFORMATION_SCHEMA.COLUMNS
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)'
        )
    BEGIN
        SET @varcharDataType = @varcharDataType + '('
            + CAST(@Datasize AS VARCHAR(50)) + ')'
    END

INSERT  INTO #udtflds
        SELECT  TABLE_SCHEMA,
                TABLE_NAME,
                Column_Name
        FROM    INFORMATION_SCHEMA.COLUMNS
        WHERE   Domain_name = @udt
                AND Domain_schema = @udtschema

DECLARE @exec VARCHAR(500)

DECLARE alter_cursor CURSOR
    FOR SELECT  Schemaname,
                TableName,
                FiledName
        FROM    #udtflds

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

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

    END

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

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

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

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

CLOSE alter_cursor
DEALLOCATE alter_cursor
SELECT  *
FROM    #udtflds

DROP TABLE #udtflds

1: http://www.sql-server-performance.com/2008/how-to-alter-a-uddt/ has replaced http://www.sql-server-performance.com/faq/How_to_alter_a%20_UDDT_p1.aspx

Solution 4 - Sql

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.

Solution 5 - Sql

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.

Solution 6 - Sql

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

Solution 7 - Sql

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.

Solution 8 - Sql

I ran into this issue with custom types in stored procedures, and solved it with the script below. I didn't fully understand the scripts above, and I follow the rule of "if you don't know what it does, don't do it".

In a nutshell, I rename the old type, and create a new one with the original type name. Then, I tell SQL Server to refresh its details about each stored procedure using the custom type. You have to do this, as everything is still "compiled" with reference to the old type, even with the rename. In this case, the type I needed to change was "PrizeType". I hope this helps. I'm looking for feedback, too, so I learn :)

Note that you may need to go to Programmability > Types > [Appropriate User Type] and delete the object. I found that DROP TYPE doesn't appear to always drop the type even after using the statement.

/* Rename the UDDT you want to replace to another name */ 
exec sp_rename 'PrizeType', 'PrizeTypeOld', 'USERDATATYPE';

/* Add the updated UDDT with the new definition */ 
CREATE TYPE [dbo].[PrizeType] AS TABLE(
	[Type] [nvarchar](50) NOT NULL,
	[Description] [nvarchar](max) NOT NULL,
	[ImageUrl] [varchar](max) NULL
);

/* We need to force stored procedures to refresh with the new type... let's take care of that. */
/* Get a cursor over a list of all the stored procedures that may use this and refresh them */
declare sprocs cursor
  local static read_only forward_only
for
	select specific_name from information_schema.routines where routine_type = 'PROCEDURE'

declare @sprocName varchar(max)

open sprocs
fetch next from sprocs into @sprocName
while @@fetch_status = 0
begin
	print 'Updating ' + @sprocName;
	exec sp_refreshsqlmodule @sprocName
	fetch next from sprocs into @sprocName
end
close sprocs
deallocate sprocs

/* Drop the old type, now that everything's been re-assigned; must do this last */
drop type PrizeTypeOld;

Solution 9 - Sql

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

Solution 10 - Sql

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! ;)

Content Type	Original Author	Original Content on Stackoverflow
Question	bzamfir	View Question on Stackoverflow
Solution 1 - Sql	Philip Fourie	View Answer on Stackoverflow
Solution 2 - Sql	Konektiv	View Answer on Stackoverflow
Solution 3 - Sql	Robin	View Answer on Stackoverflow
Solution 4 - Sql	phosplait	View Answer on Stackoverflow
Solution 5 - Sql	Pete	View Answer on Stackoverflow
Solution 6 - Sql	devio	View Answer on Stackoverflow
Solution 7 - Sql	mcfea	View Answer on Stackoverflow
Solution 8 - Sql	Auri Rahimzadeh	View Answer on Stackoverflow
Solution 9 - Sql	Ramanjaneyulu	View Answer on Stackoverflow
Solution 10 - Sql	Greg	View Answer on Stackoverflow