TSQL Script to Find the Names of Stored Procedures that Use XQUERY

Below scripts can be used to find the names of Stored Procedures that use XQUERY :


--Method 1:

SELECT  SP_Name StoredProcedure FROM (
SELECT schema_name(schema_id)+'.'+name SP_Name FROM sys.procedures ) tmp
CROSS APPLY sys.dm_sql_referenced_entities (SP_Name, 'OBJECT')
WHERE referenced_entity_name IN ( 'value','query')
AND is_ambiguous = 1


--Method 2:


SELECT schema_name(schema_id)+'.'+object_name(referencing_id) StoredProcedure
FROM sys.sql_expression_dependencies  SED
JOIN sys.procedures P 
ON P.object_id = SED.referencing_id
WHERE referenced_entity_name IN ( 'value','query')
AND is_ambiguous = 1

Reference :


See Also :



No comments: