Table of Contents



This script was developed to answer the question in this thread: I need query find all the SPs that used dynamic SQL

We can execute dynamic SQL using sp_executesql or just with Exec / Execute.

To find the names of the StoredProcedure that may have used dynamic SQL, this script can be used:

SELECT Schema_name(Schema_id)+'.'+Object_Name(M.Object_id) StoredProceduresWithDynamicSQL
FROM sys.sql_modules M 
JOIN  sys.objects O ON M.object_id = O.object_id 
WHERE definition LIKE '%CREATE PROC%'
AND (definition LIKE '%SP_ExecuteSQL%' OR definition LIKE '%EXEC%')

But Exec / Execute can be used inside a stored procedure to call another stored procedure or to execute a dynamic SQL. So, to eliminate the stored procedure names referencing another stored procedure and to find the names of the stored procedure that has used Exec / Execute only to execute dynamic SQL,
the following script can be used:

SELECT Schema_name(Schema_id)+'.'+Object_Name(M.Object_id) StoredProceduresWithDynamicSQL
FROM sys.sql_modules M 
JOIN  sys.objects O ON M.object_id = O.object_id 
WHERE definition LIKE '%CREATE PROC%'
AND (definition LIKE '%SP_ExecuteSQL%' OR definition LIKE '%EXEC%'
EXCEPT
SELECT StoredProcedure  FROM
SELECT Schema_name(Schema_id)+'.'+Object_Name(M.Object_id) StoredProcedure 
FROM sys.sql_modules M 
JOIN  sys.objects O ON M.object_id = O.object_id 
WHERE definition LIKE '%CREATE PROC%'
AND (definition LIKE '%SP_ExecuteSQL%' OR definition LIKE '%EXEC%')) tmp 
CROSS APPLY sys.dm_sql_referenced_entities (StoredProcedure, 'OBJECT');

The above script will not work under the following scenarios:
  • if we have used Exec / Execute inside a stored procedure for both purposes, i.e. to call another stored procedure and to execute dynamic SQL.
  • if we have used sp_executesql or Exec / Execute and commented it inside a stored procedure.
The above scripts will still be useful because we don't have any other direct way to find the names of the stored procedure that has used dynamic SQL.

This script also won't work for encrypted procedures.


See Also


Other Languages