How To Alter User-Defined Table Types in SQL Server

I cannot use ALTER to modify the type nor can I drop and recreate it since it is being referenced by existing Stored Procedures. That means, reference must be removed first. In this post, We will learn how to alter User-Defined Table types in SQL Server.

Example Case:

Let’s say we have multiple stored procedure (e.g: spGetEmployee,spGetAllEmployee etc) referenced to a table type “oldTableType” with definition as shown below:

1
2
3
4
5
CREATE TYPE OldTableType AS TABLE 
(
	ID INT,
	Name VARCHAR(50)
)

And now we want to modify it as shown below:

1
2
3
4
5
6
CREATE TYPE OldTableType AS TABLE 
(
	ID INT,
	FirstName VARCHAR(50),
	LastName VARCHAR(50)
)

In order to alter user-defined table type, we need to drop all the stored procedure (e.g : spGetEmployee,spGetAllEmployee) and re-create the table type definition.

This can be a complex task to do  if we have multiple such dependency every time for a small change in the table type.

See the below steps to alter user-defined table type:

    1. Rename the table type to a new one using ‘sp_rename’.
    2. Create a new table type with old name with the changes you need to the table type.
    3. Loop through all the dependency and execute sp_refreshsqlmodule on it.
    4. Drop the renamed table type.

Code Example :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
 
-- Rename the table type to a new one
EXEC sys.sp_rename 'dbo.OldTableType', 'NewTableType', 'userdatatype';
GO
 
-- Create a new table type with old name with the changes you need to the table type.
CREATE TYPE OldTableType AS TABLE 
(
	ID INT,
	FirstName VARCHAR(50),
	LastName VARCHAR(50)
)
GO
 
-- Loop through all the dependency and execute sp_refreshsqlmodule on it
DECLARE @Name NVARCHAR(500);
 
DECLARE DEP_CURSOR CURSOR FOR
SELECT referencing_schema_name + '.' + referencing_entity_name
FROM sys.dm_sql_referencing_entities('dbo.OldTableType', 'TYPE');
 
OPEN DEP_CURSOR;
 
FETCH NEXT FROM DEP_CURSOR INTO @Name;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    EXEC sys.sp_refreshsqlmodule @name = @Name;
    FETCH NEXT FROM DEP_CURSOR INTO @Name;
END;
 
CLOSE DEP_CURSOR;
DEALLOCATE DEP_CURSOR;
GO
 
-- Drop the renamed table type
DROP TYPE dbo.NewTableType;
GO

If you have any query then please comment below and let us know. If you liked this article, then please follow us on Facebook to get notification for new posts.

Happy Learning 🙂

Rahul is a Data Geek, technology enthusiast, a passionate writer, thinker with passion for computer programming.  He loves to explore technology and finds ultimate joy when writing about trending technology, geek stuff and web development.