查询正在执行的SQL语句DBCCINPUTBUFFER

DBCC INPUTBUFFER 返回进程下SQL语句

如果查询所有的进程如何呢？

创建一个存储过程

CREATE proc pr_dbccINPUTBUFFER(@spid varchar(200))
as
declare @tsql varchar(4000)
select @tsql='DBCC INPUTBUFFER ('+cast(@spid as varchar(500))+')'
exec (@tsql)

创建存放SQL的表

 create table logs..temprunsql
 (eventtype varchar(200),para varchar(200),tsql varchar(max))

 

执行的语句

truncate table logs..temprunsql

if OBJECT_ID('tempdb..#temp_pros') is not null drop table #temp_pros
select spid, IDENTITY(int,1,1) id into #temp_pros from sysprocesses
where lastwaittype ='MISCELLANEOUS'
declare @a int
declare @tsql varchar(4000)
set @a=1
while (@a <(select COUNT(*) from #temp_pros))
begin
select @tsql='
insert into logs..temprunsql
exec wfp..pr_dbccINPUTBUFFER '+cast(spid as varchar(500))+' '
from #temp_pros where id=@a
set @a =@a +1;
exec (@tsql)

end

select * from logs..temprunsql

 

转载于:https://www.cnblogs.com/cwfsoft/p/3417368.html