DECLARE
@tblDynamicQuery
TABLE
(ID
INT
identity(1,1), ProcToExecuteDynSQL
VARCHAR
(500))
INSERT
INTO
@tblDynamicQuery(ProcToExecuteDynSQL)
values
(
'EXEC'
)
'EXECUTE'
'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
sc.id
syscomments sc
JOIN
@tblDynamicQuery dsql
on
sc.TEXT
like
'%'
+ dsql.ProcToExecuteDynSQL +
--WHERE TEXT LIKE '%sp_executesql%'
AND
xtype =
'P'
For
Procedure
Naomi N edited Revision 4. Comment: Minor edit
Naomi N edited Revision 1. Comment: Changed title
Naomi N edited Original. Comment: This article needs some work
Similar to: social.technet.microsoft.com/.../18790.t-sql-script-to-find-the-names-of-the-stored-procedures-that-used-dynamic-sql.aspx
Hi Prajesh,
Where is another article on this exact topic. Do you think it's OK to delete this article? See Ed's comment
Actually this was publish first "FIRST PUBLISHED BY... When: 25/7/2013 11:05 PM"
and the other one "FIRST PUBLISHED BY... When: 30/7/2013 7:08 AM"