Find UDT given spnames

SELECT
coalesce(object_schema_name(Referencing_ID)+’.’,”)+ –likely schema name
object_name(Referencing_ID)+ –definite entity name
coalesce(‘.’+col_name(referencing_ID,referencing_minor_id),”)
AS [referencing],
coalesce(Referenced_server_name+’.’,”)+ –possible server name if cross-server
coalesce(referenced_database_name+’.’,”)+ –possible database name if cross-database
coalesce(referenced_schema_name+’.’,”)+ –likely schema name
coalesce(referenced_entity_name,”) + –very likely entity name
coalesce(‘.’+col_name(referenced_ID,referenced_minor_id),”)AS [referenced],*
FROM sys.sql_expression_dependencies d
INNER JOIN sys.objects O ON d.referencing_id=O.object_id
WHERE O.name IN
(‘spname’)
and referenced_entity_name LIKE ‘UDT%’
ORDER BY [referenced]
Advertisements