ALTER TYPE Not Available So How To Alter a Type?

I know there are other questions out there regarding this issue, but this one is really simple so I'm not looking for a complicated answer!

So I have this script:

IF  EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'IDListTableType' AND ss.name = N'dbo')
DROP TYPE [dbo].[IDListTableType]
GO

IF NOT EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'IDListTableType' AND ss.name = N'dbo')
CREATE TYPE [dbo].[IDListTableType] AS TABLE(
    [ID] [int] NOT NULL,
    UNIQUE NONCLUSTERED 
(
    [ID] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO

I get an error when I run it because IDListTableType is referenced elsewhere in the database. So I need a way to either alter the table type in place, or to find and drop and recreate all other database objects that are referencing IDListTableType. Is there a simple way to do this?

Simplest would be to create a new type with the updated definition, then you can change the affected objects one at a time (or all at once) to reference the new name. You can’t pull the tablecloth.

I wrote an article about this here:

• How to Alter User Defined Table Type in SQL Server

The fundamental process is:

• create a new type

• determine which objects reference the old type

• change those objects manually to point at the new type (or at least those objects that need to use the new type)

• optionally, drop the old type

The tricky part is #2, people will hunt and peck manually, or try to parse OBJECT_DEFINITION or sys.sql_modules, but the following is a bit more reliable:

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

StackExchange Database Administrators Q#242497, answer score: 6