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