T-SQL find specific user-defined table type in all stored procedures

December 11, 2023

I must delete and create a user-defined table type. This type is being used in different stored procedures.

Is there a way to find all stored procedures where the type is in use?
So that can I be sure that I find all stored procedures.

Thanks.

I knew this actually.

select * 
from sys.table_types 
where name = 'typeLT_LiefertreueLieferant'

so I get the system_type_id, but I have no way to the components in the stored procedure.

>Solution :

Below T-SQL might work for you:

SELECT ROUTINE_NAME, ROUTINE_DEFINITION, ROUTINE_TYPE 
FROM INFORMATION_SCHEMA.ROUTINES as ISR
WHERE ROUTINE_DEFINITION LIKE '%typeLT_LiefertreueLieferant%' AND ROUTINE_TYPE='PROCEDURE' 
order by ROUTINE_DEFINITION

And If wanted to check for all table type then below T-SQL will help:

SELECT ROUTINE_NAME, ROUTINE_DEFINITION, ROUTINE_TYPE 
FROM INFORMATION_SCHEMA.ROUTINES as ISR
cross join sys.table_types  TY
WHERE ROUTINE_DEFINITION LIKE '%'+ TY.name +'%' AND ROUTINE_TYPE='PROCEDURE' 
order by ROUTINE_DEFINITION