017. Find stored procedures which have used Dynamic Sql

SQL Downloads, SQL SERVER, Sql Server Scripts

Whenever we are finding some issues in performance, we are also try to avoid Dynamic Sql in Stored Procedure.
To avoid sql injection we should not write dynamic sql.

-- Find stored procedures which have used Dynamic Sql
SELECT  
	SP_Name
	,error_message
	,error_type_desc 
FROM (SELECT schema_name(schema_id)+'.'+name SP_Name FROM sys.procedures) AS T
CROSS APPLY sys.dm_exec_describe_first_result_set (SP_Name, NULL, 0) 
WHERE error_type =4