How to Alter User Defined Table Type in SQL Server

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

This gives this error:

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;

This gives this error:

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:

Resources

Community

Subscribe