Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to Alter User Defined Table Type in SQL Server


By:   |   Updated: 2019-08-08   |   Comments   |   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 Product Manager at SentryOne, 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 serves as a community moderator for the Database Administrators Stack Exchange.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools