This article has the script that will be useful to find the names of the Stored procedure that has used dynamic sql within the definition of the Stored procedure .
In this post , I am creating a sample database with four stored procedures to show an example on how the script works :
Step 1: Create sample database
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'ProcsWithDynamicSQL') BEGIN DROP DATABASE ProcsWithDynamicSQL END ELSE CREATE DATABASE ProcsWithDynamicSQL
Step 2: Create Stored procedures
USE [ProcsWithDynamicSQL] GO --ProcWith_SP_ExecuteSQL - Stored procedure that has used SP_ExecuteSQL to execute dynamic SQL CREATE PROCEDURE ProcWith_SP_ExecuteSQL AS BEGIN DECLARE @SQLString NVARCHAR(20)= 'SELECT GETDATE()' EXECUTE sp_executesql @SQLString END GO --ProcWith_ExecuteDynamicSQL - Stored procedure that has used Execute/EXEC to execute dynamic SQL CREATE PROC ProcWith_ExecuteDynamicSQL AS BEGIN DECLARE @SQLString NVARCHAR(20)= 'SELECT GETDATE()' EXEC (@SQLString ) END GO --ProcWith_DynamicSQL - Stored procedure that has used dynamic SQL string CREATE PROC ProcWith_DynamicSQL AS BEGIN EXEC ('SELECT GETDATE()' ) END GO --Proc_Calling_AnotherProc - Stored procedure calls another Stored procedure within its definition CREATE PROCEDURE Proc_Calling_AnotherProc AS BEGIN EXEC ProcWith_DynamicSQL END GO
USE [ProcsWithDynamicSQL] GO 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');
If you notice the below screenshot ,in the result set of the query Stored procedure name “Proc_Calling_AnotherProc”
will be eliminated and only the names of the Stored Procedure that has used dynamic SQL will be displayed .
Leave a Reply