다음을 통해 공유

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*/