How to do ALTER TABLE TYPE efficiently

Unlike tables, a table type cannot be altered by a simple command. I need to do the following:

• Rename existing table type

• Create the new table type

• Open each affected objects (i.e. ALTER SP) and execute it again so that the reference to table type will be updated.

But this is a tedious task as there may be many SP to be updated. Is there any easy way/pre-generated script that can automate this process?

Instead of


Open each affected objects (i.e. ALTER SP) and execute it again so
that the reference to table type will be updated

you can just find all the affected objects and refresh them using sp_refreshsqlmodule.

Here is the code to find the modules:

select object_name(referencing_id)
from sys.sql_expression_dependencies
where referenced_class_desc = 'TYPE' and referenced_entity_name =  'your_table_type_name';

To do refresh of all this objects you can simply generate the commands like this:

select 'sp_refreshsqlmodule ' + quotename( object_name(referencing_id), '''')
from sys.sql_expression_dependencies
where referenced_class_desc = 'TYPE' and referenced_entity_name =  'your_table_type_name';

Make the output to text instead of grid and copy + paste generated rows to SSMS and execute them.

StackExchange Database Administrators Q#196482, answer score: 9