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?
Advertisement
Answer
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';