T-SQL: How to Find Stored Procedures that Use Dynamic SQL

T-SQL: How to Find Stored Procedures that Use Dynamic SQL

Dynamic SQL is a programming technique that enables you to build SQL statements dynamically at runtime.

You can create more general purpose, flexible applications by using dynamic SQL because the full text of a SQL statement may be unknown at compilation.

For example, dynamic SQL lets you create a procedure that operates on a table whose name is not known until runtime.

There may be several methods of implementing this in SQL Server

During the development cycle, when necessary who write dynamic queries and execute it inside stored procedures.

The query below helps to find the stored procedures that use dynamic queries:

DECLARE @tblDynamicQuery TABLE (ID INT identity(1,1), ProcToExecuteDynSQL VARCHAR(500))
INSERT INTO @tblDynamicQuery(ProcToExecuteDynSQL) values('EXEC')
INSERT INTO @tblDynamicQuery(ProcToExecuteDynSQL) values('EXECUTE')
INSERT INTO @tblDynamicQuery(ProcToExecuteDynSQL) values('SP_EXECUTESQL')
You can add as many procs in ProcToExecuteDynSQL
for dynamic query execution including user defined Stored proc
FROM sysobjects
SELECT sc.id
FROM syscomments sc JOIN @tblDynamicQuery dsql
on sc.TEXT like '%' + dsql.ProcToExecuteDynSQL + '%'
--WHERE TEXT LIKE '%sp_executesql%'
AND xtype = 'P'/*For Procedure*/

Leave a Comment
  • Please add 2 and 6 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
  • Naomi  N edited Original. Comment: This article needs some work

  • Naomi  N edited Revision 1. Comment: Changed title

  • Naomi  N edited Revision 4. Comment: Minor edit

Page 1 of 1 (3 items)
Wikis - Comment List
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Page 1 of 1 (7 items)