How to get list of custom user types for listed stored procedures?-CodePudding

I was able to find way to find custom user types for one stored procedure:

select referenced_entity_name
from sys.dm_sql_referenced_entities('#procedure name here#', 'OBJECT')
where referenced_class_desc = 'TYPE'

How should I use this function for comma separated list of stored procedures?

CodePudding user response：

If you have it available (SQL Server 2016 and newer), you can use STRING_SPLIT to split your list and then use CROSS APPLY to call the function for each of the items in the list.

DECLARE @list VARCHAR(1000) = 'p_Proc1,p_Proc2';

SELECT referenced_entity_name
FROM STRING_SPLIT(@list, ',') ss
CROSS APPLY sys.dm_sql_referenced_entities(ss.value, 'OBJECT')
where referenced_class_desc = 'TYPE';