T-SQL: Script to Find the Names of Stored Procedures that Use Dynamic SQL ( Method 2 )

First method was using dynamic management function - sys.dm_sql_referenced_entities .

Fun with system functions continues ...

Here is the second method : Using dynamic management function - sys.dm_exec_describe_first_result_set

SELECT  SP_Name,error_message,error_type_desc FROM (
SELECT schema_name(schema_id)+'.'+name SP_Name FROM sys.procedures ) tmp
CROSS APPLY sys.dm_exec_describe_first_result_set (SP_Name, NULL, 0)
WHERE error_type = 4

See Also :

• SQL Server - Dynamic SQL - SQL Injection - EXEC [ QUOTENAME() , REPLACE() , EXECUTE AS ] - Sp_executesql - [ RECOMPILE ]
• T-SQL Script to find Stored Procedures that Use DYNAMIC_SQL , TEMPORARY_TABLE