USE tempdb
GO

--创建测试表
IF OBJECT_ID('tempdb.dbo.#tbl_SPWho') IS NOT NULL
    BEGIN
        DROP TABLE tempdb.dbo.#tbl_SPWho
    END

CREATE TABLE tempdb.dbo.#tbl_SPWho
    (
      spid SMALLINT ,
      ecid SMALLINT ,
      [status] NVARCHAR(30) ,
      loginame NVARCHAR(128) ,
      hostName NVARCHAR(128) ,
      blk CHAR(5) ,
      dbname NVARCHAR(128) ,
      cmd NVARCHAR(16) ,
      request_id INT
    )

--从系统存储过程中获取数据并插入临时表中
INSERT  INTO tempdb.dbo.#tbl_SPWho
        EXEC sp_who
GO

--创建存放sp_who2信息的临时表
IF OBJECT_ID('tempdb.dbo.#tbl_SPWho2') IS NOT NULL
    BEGIN
        DROP TABLE tempdb.dbo.#tbl_SPWho2
    END

CREATE TABLE tempdb.dbo.#tbl_SPWho2
    (
      spid SMALLINT ,
      [status] NVARCHAR(30) ,
      [login] NVARCHAR(128) ,
      HostName NVARCHAR(128) ,
      BlkBy CHAR(5) ,
      DBName NVARCHAR(128) ,
      Command NVARCHAR(16) ,
      CPUTime INT ,
      DiskIO INT ,
      LastBatch NVARCHAR(50) ,
      ProgramName NVARCHAR(100) ,
      SPID2 SMALLINT ,
      REQUESTID INT
    )

--从系统存储过程中获取数据并插入临时表中
INSERT  INTO tempdb.dbo.#tbl_SPWho2
        EXEC sp_who2
GO

--查看特定数据库的数据
SELECT  spid AS SessionID ,
        ecid AS ExecutionContextID ,
        [status] AS ProcessStatus ,
        loginame AS LoginName ,
        hostname AS HostName ,
        blk AS BlockedBy ,
        dbname AS DatabaseName ,
        cmd AS CmomandType ,
        request_id AS RequestID
FROM    dbo.#tbl_SPWho
WHERE   dbname = 'AdventureWorks'
GO

--仅查看阻塞的数据
SELECT  spid AS SessionID ,
        ecid AS ExecutionContextID ,
        [status] AS ProcessStatus ,
        loginame AS LoginName ,
        hostname AS HostName ,
        blk AS BlockedBy ,
        dbname AS DatabaseName ,
        cmd AS CmomandType ,
        request_id AS RequestID
FROM    dbo.#tbl_SPWho
WHERE   blk > 0
GO

--查看挂起的数据
SELECT  spid AS SessionID ,
        ecid AS ExecutionContextID ,
        [status] AS ProcessStatus ,
        loginame AS LoginName ,
        hostname AS HostName ,
        blk AS BlockedBy ,
        dbname AS DatabaseName ,
        cmd AS CmomandType ,
        request_id AS RequestID
FROM    dbo.#tbl_SPWho
WHERE   [STATUS] = 'suspended'
GO