T-SQL Script to find the names of the StoredProcedure that has used dynamic SQL

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 .

One thought on “T-SQL Script to find the names of the StoredProcedure that has used dynamic SQL”

Leave a Reply

Your email address will not be published. Required fields are marked *

*