Resources For IT Professionals
Page Details
  • First published by (8Microsoft Community Contributo)
  • When: 29 Jul 2013 9:08 PM
  • Last revision by (eMicrosoft Partne)
  • When: 11 Sep 2013 3:28 PM
  • Revisions: 12
  • Comments: 5
Options

Revision #9

Wiki  >  TechNet Articles  >  T-SQL: Script to Find the Names of Stored Procedures that Use Dynamic SQL  >  Revision #9
T-SQL: Script to Find the Names of Stored Procedures that Use Dynamic SQL
You are currently reviewing an older revision of this page.
Go to current version
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 don't work for encrypted procedures.

See also :
http://social.technet.microsoft.com/wiki/contents/articles/17785.sql-server-query-language-transact-sql.aspx
Revert to this revision