How to Alter User Defined Table Type in SQL Server


By:   |   Updated: 2019-08-08   |   Comments (5)   |   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, October 28, 2020 - 11:52:17 AM - Aaron Bertrand Back To Top (86707)
Max, of course. While I don't imagine there are a whole lot of cases where you're using a table type in dynamic SQL, it's certainly possible. On the plus side, if the code that references the old version of the type doesn't get updated to use the changes to the type, maybe it's ok that it continues to use the old version. And maybe that's a risk you take using dynamic SQL - some things you're just not going to be able to discover until they break.

This article also doesn't explain how to update all of your application code, either, which surely has mentions of the type name and data types that might be equally hard to find depending on how the source code creates those references, whether/how you're using source control, etc. No article can solve every peripheral problem without becoming a book.

Wednesday, October 28, 2020 - 11:24:30 AM - Max Back To Top (86706)
It goes without saying that if you have dynamic sql code referencing you type it won't work.
Especially if you build your referenced type at runtime so you are unable to find it even with a text search in procedures scripts.

Wednesday, October 21, 2020 - 3:13:52 PM - Aaron Bertrand Back To Top (86674)
gserdijn, yes there are elegant ways to change everything all at once.

But that's not always what you want to do - sometimes there is a benefit to phase in two different versions. Say you're adding or dropping a column or changing the type of a column, but you don't have the luxury of changing all the application code to start/stop referencing that column or change the variables feeding into it.

Sometimes a change won't require a phased-in approach, but sometimes it will.

Wednesday, October 21, 2020 - 2:44:55 PM - gserdijn Back To Top (86673)
My own solution was to collect the procedures and their code in which the type was referenced. Then drop the procedures, drop the type, recreate the type, and recreate the procedures.

DECLARE @Dependencies TABLE (id INT IDENTITY, referencing_entity_name SYSNAME, object_code NVARCHAR(MAX));
INSERT INTO @Dependencies (referencing_entity_name, object_code)
SELECT /* get the procedures and the code */
referencing_schema_name + '.' + referencing_entity_name,
OBJECT_DEFINITION( OBJECT_ID (referencing_schema_name + '.' + referencing_entity_name))
FROM sys.dm_sql_referencing_entities('<typename>', 'TYPE');

No code changes necessary, but there might be some issues with rights due to dropping of procedures.

But today I saw a post by Michael J. Swart which offered an elegant solution for this problem: sp_rename + sp_refreshsqlmodule.
https://michaeljswart.com/2020/10/how-to-alter-user-defined-table-types-mostly-online/

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

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