How to Alter User Defined Table Type in SQL Server


By:   |   Updated: 2019-08-08   |   Comments (1)   |   Related: More > User Defined Type UDT

Problem

Since the advent of table-valued parameters in SQL Server 2008, table types have become more and more popular. Unfortunately, once a table type is actively being referenced by one or more objects, it is cumbersome to change. There is no ALTER TYPE, and you can’t drop and re-create a type that is in use. Is there an easy way to change all of the referencing objects?

Solution

The simplest solution is to create a new table type, change all the objects that reference the old type, then drop the old type. Let’s start with the simple scenario of a table type referenced by two stored procedures:

CREATE TYPE dbo.MyType AS TABLE (id int);
GO CREATE PROCEDURE dbo.MyProcedure1
  @tvp dbo.MyType READONLY
AS
BEGIN
  SELECT id FROM @tvp;
END
GO CREATE PROCEDURE dbo.MyProcedure2
AS
BEGIN
  DECLARE @tvp dbo.MyType;
END
GO

Now, let’s say you want to change the table type so that the id column supports the bigint data type. As mentioned above, SQL Server does not have an ALTER TYPE command, so this does not work:

ALTER TYPE dbo.MyType AS TABLE (id bigint);

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'TYPE'.

Your next attempt would be to drop the type and re-create it, but that won’t work either:

DROP TYPE dbo.MyType;

Msg 3732, Level 16, State 1, Line 1
Cannot drop type 'dbo.MyType' because it is being referenced by object 'MyProcedure1'. There may be other objects that reference this type.

The error message gives away what your next problem will be: changing MyProcedure1 and then getting a new error message about MyProcedure2. Depending on how much you’ve used this type, that could be a long and tedious process as you discover, one by one, all of the objects affected.

So, to limit disruptiveness, let’s just create a new type:

CREATE TYPE dbo.MyOtherType AS TABLE(id bigint);			

We can easily discover all the objects that reference the original type using the following metadata query:

SELECT s.name, o.name, def = OBJECT_DEFINITION(d.referencing_id) 
  FROM sys.sql_expression_dependencies AS d
  INNER JOIN sys.objects AS o
     ON d.referencing_id = o.[object_id]
  INNER JOIN sys.schemas AS s
     ON o.[schema_id] = s.[schema_id]
  WHERE d.referenced_database_name IS NULL
    AND d.referenced_schema_name = N'dbo'
    AND d.referenced_entity_name = N'MyType';

This will produce the following result:

Results from metadata query

Now you know exactly which objects to change. Alter their definitions to point to the new type, then you can drop the old type. Application code won’t have to change, unless you also change the name of the parameter:

ALTER PROCEDURE dbo.MyProcedure1
  @tvp dbo.MyOtherType READONLY
AS
BEGIN
  SELECT id FROM @tvp;
END
GO ALTER PROCEDURE dbo.MyProcedure2
AS
BEGIN
  DECLARE @tvp dbo.MyOtherType;
END
GO DROP TYPE dbo.MyType;

If you want to keep the original type name, you could repeat the process: re-create it with the new definition, then alter all the objects again, back to the old type name.

Next Steps

Read on for related tips and other resources:



Last Updated: 2019-08-08


get scripts

next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

View all my tips





Comments For This Article




Wednesday, April 29, 2020 - 3:59:14 AM - Jaydev Solanki Back To Top

This is not a good practice to change the name of table value type because in case there are multiple developer working on the same project and if the table value type is mentioned as a parameter in server side code than changing the naming convention of table value type will break the code. 

Although it works when you dont give a concern regarding server side code. 



download


Recommended Reading

SQL Server User Defined Type To Store and Process Email Addresses





get free sql tips
agree to terms


Learn more about SQL Server tools