T-SQL: Script to Find the Names of Stored Procedures that Use Dynamic SQL

T-SQL: Script to Find the Names of Stored Procedures that Use Dynamic SQL

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 a dynamic SQL and the other scenario: if we have used sp_executesql or Exec / Execute  and commented it inside a Stored procedure ,
but still the above scripts will 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


Leave a Comment
  • Please add 8 and 6 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • Richard Mueller edited Revision 9. Comment: Modified title, fixed minor typo

  • Naomi  N edited Revision 3. Comment: Minor edit

  • Naomi  N edited Revision 2. Comment: Minor edit

Page 1 of 1 (3 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
Page 1 of 1 (5 items)