SQL Server: Query SysProcesses and InputBuffer

Question

在查询SYSPROCESSES表时，我试图为数据库返回的每个记录提取输入缓冲区数据(DBCC INPUTBUFFER(@SPID)(。我很想知道是否有更好的方法来实现这一点，但也希望能纠正我目前为学习目的所做的工作。

DECLARE @Max [int]
DECLARE @Min [int] = 1
SELECT @Max = COUNT(SPID)
FROM MASTER.DBO.SYSPROCESSES
WHERE DB_NAME(DBID) = 'Northwinds' AND DBID != 0
CREATE TABLE #Results (
    EventType [nvarchar](1024),
    Parameters [int],
    EventInfo [nvarchar](1024),
    SPID [int],
    STATUS [nvarchar](255),
    PROGRAM_NAME [nvarchar](1024),
    CMD [nvarchar](255),
    LOGINAME [nvarchar](255)
)
WHILE @Min <= @Max
BEGIN
    DECLARE @SPID [int]
    WITH SelectedRow AS (
        SELECT SPID, ROW_NUMBER() OVER (ORDER BY SPID) AS RowNumber
        FROM MASTER.DBO.SYSPROCESSES
        WHERE DB_NAME(DBID) = 'Northwinds' AND DBID != 0
    )
    SELECT @SPID = SPID
    FROM SelectedRow
    WHERE RowNumber = @Min
    DECLARE @InputBuffer TABLE (
        EventType [nvarchar](1024),
        Parameter [int],
        EventInfo [nvarchar](1024)
    )
    DECLARE @SysProcesses TABLE (
        SPID [int],
        STATUS [nvarchar](255),
        PROGRAM_NAME [nvarchar](1024),
        CMD [nvarchar](255),
        LOGINAME [nvarchar](255)
    )
    INSERT @InputBuffer 
        EXEC('DBCC INPUTBUFFER('+@SPID+')')
    INSERT @SysProcesses 
        SELECT SPID, STATUS, PROGRAM_NAME, CMD, LOGINAME
        FROM MASTER.DBO.SYSPROCESSES
        WHERE DB_NAME(DBID) = 'Northwinds' AND DBID != 0 AND SPID = @SPID
    INSERT INTO #TempResults(EventType, Parameters, EventInfo, SPID, STATUS, PROGRAM_NAME, CMD, LOGINAME)
        SELECT *
        FROM @InputBuffer, @SysProcesses
    SET @Min = (@Min + 1)
END

现在，当我执行以下查询时：

SELECT SPID
FROM MASTER.DBO.SYSPROCESSES
WHERE DB_NAME(DBID) = 'Northwinds' AND DBID != 0

它返回31行。。。

然而，当我在执行上述循环后SELECT * FROM #TempResults时，我在临时表中返回大量重复。。。总计10751。

同样，这主要是为了学习，但也将是一个非常方便的功能。我正在寻找导致返回重复记录的信息，以及可能的更好的解决方案。

Answer 1

您正在执行以下操作：

INSERT #Results
SELECT * 
FROM @InputBuffer, @SysProcesses

这实际上是一个交叉联接，所以如果任何一个表都有多行，那么结果就会有COUNT(left side) * COUNT(right side)行。也许你应该使用SELECT DISTINCT column list，或者，正如我在评论中建议的那样，停止循环使用sysprocesses(已弃用(和从DBCC INPUTBUFFER检索信息——即使是出于教育目的，这也是痛苦的。

Answer 2

请参阅以下查询以获得更好的结果

DEclare @SPID [int]
DEclare @database_name [nvarchar](1024)
DEclare @hostname [nvarchar](1024)
DEclare @PROGRAM_NAME [nvarchar](1024)
DEclare @LOGINNAME [nvarchar](255)
DEclare @Inputbuffer [nvarchar] (4000)
CREATE TABLE #TempResults (
  SPID [int],
  database_name [nvarchar](1024),
  hostname [nvarchar](1024),
  PROGRAM_NAME [nvarchar](1024),
  LOGINAME [nvarchar](255),
  EventType [nvarchar](1024),
  Parameters [int],
  EventInfo [nvarchar](4000)
)

DECLARE db_cursor CURSOR FOR  
select spid,db_name(dbid), hostname,program_name,loginame from sys.sysprocesses where dbid in(11,10,13,14,15,16)  
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @spid,@database_name,@hostname,@program_name,@loginname   
WHILE @@FETCH_STATUS = 0   
BEGIN   
     create table #InputBuffer1  (
    EventType [nvarchar](1024),
    Parameter [int],
    EventInfo [nvarchar](4000)
)
    INSERT into #InputBuffer1 
    EXEC('DBCC INPUTBUFFER('+@SPID+')')
create TABLE #SysProcesses  (
    SPID [int],
database_name [nvarchar](1024),
hostname [nvarchar](1024),
PROGRAM_NAME [nvarchar](1024),
LOGINAME [nvarchar](255)
)

INSERT INTO #SysProcesses(spid,database_name,hostname,program_name,LOGINAME) 
    SELECT  @spid,@database_name,@hostname,@program_name,@loginname
     insert into #TempResults
     select * from #SysProcesses,#InputBuffer1
    drop table #InputBuffer1
    drop table #SysProcesses

   FETCH NEXT FROM db_cursor INTO @spid,@database_name,@hostname,@program_name,@loginname  
END   
CLOSE db_cursor   
DEALLOCATE db_cursor
select *  from #TempResults

Answer 3

可能会更晚，但对于任何感兴趣的人来说，MASTER.DBO.sysprocesss为每个并行进程返回一行，而sys.sysprocesses仅为父进程。