有哪个视图可以获取整个批处理代码

Question

batch代码为
WHILE 1=1
BEGIN
exec sp_executesql N'
SELECT * FROM [sss].[dbo].[test] WHERE [id]=@id
EXEC [dbo].[aa] @test 
EXEC [dbo].[ab] @id',    N'@test NVARCHAR(100) ,@id int',@test='你好',@id=2
END 

sys.fn_get_sql(@plan_handle)

 sys.dm_exec_sql_text(

上面两个视图，无论传入plan_handle还是sql_handle都无法获取整个批处理代码

------------------------------------------
--有时候获取到
(@test NVARCHAR(100) ,@id int)
SELECT * FROM [sss].[dbo].[test] WHERE [id]=@id
EXEC [dbo].[aa] @test
EXEC [dbo].[ab] @id
--------------------------------------
--有时候获取到
CREATE PROC aa (@test NVARCHAR(100))
AS
BEGIN
SELECT * FROM [dbo].[test] WHERE [NAME]=@test

END

-------------------------------------------------
--有时候获取到
CREATE PROC ab (@id int)
AS
BEGIN
SELECT * FROM [dbo].[test] WHERE [id]=@id

END

----------------------------------------

其实我主要想知道出问题的sql是否调用了exec sp_executesql

---

Love SQL

Answer 1

如果获取到(@test NVARCHAR(100) ,@id int) ，我就知道sql调用了exec sp_executesql

但是有时候

--------------------------------------
--有时候获取到
CREATE PROC aa (@test NVARCHAR(100))
AS
BEGIN
SELECT * FROM [dbo].[test] WHERE [NAME]=@test

END

-------------------------------------------------
--有时候获取到
CREATE PROC ab (@id int)
AS
BEGIN
SELECT * FROM [dbo].[test] WHERE [id]=@id

END

----------------------------------------

---

Love SQL

Answer 2

批处理的应该用dbcc inputbuffer, 不过这个超过400字符的部分取不到

Answer 3

最完整的还是有 profile 跟踪

Answer 4

exec sp_executesql  只有 profiler才能看到完整的

事件类型为RPC

找了一些资料，看来是没有办法了

SQL Batch statement和Remote Procedure Call 的区别
--http://stackoverflow.com/questions/12805692/sql-server-stored-procedure-no-statements-run-when-from-rpc

TSQL event and RPC event, difference example?
--https://social.technet.microsoft.com/Forums/sqlserver/en-US/6bd9e120-305a-492b-b14b-a3b93fec7a27/tsql-event-and-rpc-event-difference-example?forum=sqldatabaseengine

---

Love SQL