By: Aaron Bertrand | Updated: 2019-08-08 | Comments (5) | Related: > 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:
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:
- Using Table Valued Parameters (TVP) in SQL Server
- Compare SQL Server Stored Procedure Performance for Table Valued Parameters vs Multiple Variables
- Streaming rows of SQL Server data to a table valued parameter using a SQLDataReader
- Table Value Parameters in SQL Server 2008 and .NET (C#)
- SQL Server Table Valued Parameters Tips
About the author
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.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2019-08-08
