Resources For IT Professionals
Post an article
Wikis - Page Details
  • First published by
  • When: 25 Jul 2013 1:05 PM
  • Last revision by (aMicrosof)
  • When: 12 Aug 2013 2:38 PM
  • Revisions: 8
  • Comments: 7
Options
RSSSubscribe to Article (RSS)
Can You Improve This Article?
Positively! Click Sign In to add the tip, solution, correction or comment that will help other users.

Report inappropriate content using these instructions.
Wiki  >  TechNet Articles  >  T-SQL: How to Find Stored Procedures that Use Dynamic SQL

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
*/
 
SELECT NAME AS SPName
,xtype
FROM sysobjects
WHERE id IN (
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*/
, , , , [Edit tags]
Leave a Comment
  • Please add 2 and 1 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • 5 Aug 2013 5:45 AM

    Naomi  N edited Revision 4. Comment: Minor edit

  • 25 Jul 2013 8:23 PM

    Naomi  N edited Revision 1. Comment: Changed title

  • 25 Jul 2013 8:18 PM

    Naomi  N edited Original. Comment: This article needs some work

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 (7 items)