. . . . . . . . . . . . . . Blog Garden' C plus plus (My technology Impire!)

存储过程的完全列表

　　SQL Server数据库管理员需要时刻保持对服务器上连接的警惕。为了某种模式的应用，管理员需要监控这些连接;列出具有问题的连接，然后采取行动来进行修整;或者是在某个选中的连接上执行操作，例如结束某个连接。这有些困难，但是不是不可能的，要执行这些任务需要使用企业管理器(Enterprise Manager)。一个忙碌的数据库管理员需要一个更加方便的工具。

　　列表1中的SQL 代码创建了一个系统存储过程，名字为sp_ListConnections。

　　这个sp_ListConnections存储过程接受8个参数，但都不是必需的。

　　第一个参数(@DBUltra)是可选的，可用来指明是否需要限制列表为只显示那些被阻塞的连接。0这个值表示，所有符合条件的连接都应该列出来。1这个值表示只有被阻塞的和正在阻塞的连接列出来。

　　第二个参数(@PCUltra)是可选的，用来指定是否需要将列表限制为只显示那些活动的连接(正在处理T-SQL语句。)0值表示，所有符合条件的连接都应该列出来。1值表示只有活动的连接显示出来。

　　随后的四个参数都是可选的，并且在一起用来形成一个通过名字作为选择关键条件的组合形式。请参考我以前写的文章，看看我对这些参数的工作方式的解释。这些参数指定了需要考虑的数据库、应用程序、登陆或者客户机。

　　第七个参数(@DBTrain)是可选的，用来指定如何应用前面四个参数。D值表示通过使用这些参数按照名字选出某个数据库。A值表示通过使用这些参数按照名字选出的是应用程序。L值表示使用这些参数选出的是登录名。其他值表示通过使用这些参数选出的是客户机的名字。

　　最后一个参数(@PCTrain)是可选的，可用来指定在某个选定的连接上执行什么操作。这个操作可以是在每个连接上执行T-SQL代码。也可以是给客户机发送消息。接收到消息的客户机就是那些包括在其他参数中的客户机。所有的@DBTrain数值都是有效的，并且发送给任何一个客户机的消息都只有一个。

　　如果@PCTrain的数值包括了字符串“@@SPID”，那么就假设它是T-SQL代码。用当前的连接ID替换了"@@SPID"之后，T-SQL代码在每个连接上执行。

　　如果@PCTrain的数值是一个简单的文本消息，那么消息就会传输到客户机上。消息可以不包含CR/LF字符。

　　如果@PCTrain数值是数字的，那么就认为这个数字是SQL Server的错误号，就会从sysmessages表中找到相应的信息发送给客户机。客户消息可以添加到sysmessages表中，通过使用sp_addmessage系统存储过程来完成(查看MSDN了解详细信息)。

　　注意:发送消息给客户机的能力依赖于Windows消息服务。SQL Server所在的机器和客户机上的这个服务必须是启动的，sp_ListConnections存储过程返回了有关SQL Server连接或者在某个连接上执行某项操作的信息。连接通过@DBUltra, @PCUltra和其他参数经过了过滤。如果@PCTrain提供的指定操作执行了，那么就不会返回有关连接信息的集合了。

　　我建议使用查询分析器(Query Analyzer)中工具菜单下的用户选项来建立正确的存储过程调用，那么它就可以通过简单的键盘组合来执行了。下面这个截屏图像演示了我的建议。

　　用户选项

　　请注意，在下面的例子中，网页的格式可能会导致某个参数值转到第二行。如果是这样的话，在执行代码之前，删除额外的CR/LF。

　　下面这个例子列出了涉及阻塞的连接的信息。

　　EXECUTE sp_ListConnections 1

　　这个例子通过SQL Agent任务系统列出了连接的信息。

　　EXECUTE sp_ListConnections 0,0,NULL,NULL,'SQLAgent%',NULL,'A'

　　这个例子列出了Northwind数据库上所有活动的连接。

　　EXECUTE sp_ListConnections 0,1,NULL,NULL,'Northwind',NULL,'D'

　　这个例子向sysmessages表中添加了客户消息。

　　EXECUTE sp_addmessage 50001,16,N'The server will be restarted in 10 minutes.'

　　这个例子给所有连接到服务器上的计算机发送了一条客户消息。

　　EXECUTE sp_ListConnections @PCTrain = '50001'

　　这个例子给连接到Northwind数据库的连接所在的计算机发送了一条文字消息。

　　EXECUTE sp_ListConnections 0,0,'Northwind',NULL,NULL,NULL,'D','The Northwind database will go offline in 10 minutes.'

　　这个例子中断了连接到Northwind数据库上的连接。

　　EXECUTE sp_ListConnections 0,0,'Northwind',NULL,NULL,NULL,'D','KILL @@SPID'

　　我希望这个系统存储过程能对你有用。

　　点击这里查看存储过程: sp_ListConnections

　　作者简介:Brian Walker i是一位高级数据库架构师，他所在的信息系统部门使用SQL Server 2000，以及.NET框架。他在IT领域有超过25年的工作经验，最近的几年他将注意力集中在数据库和SQL Server上。Walker是软件开发人员、数据库开发人员、数据库管理员和数据库顾问。他出于兴趣在业余时间开发了一些有用的软件，其中包括很大一部分SQL Server工具。

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

Stored Procedure: sp_ListConnections

--------------------------------------------------------------------
USE master
GO
CREATE PROCEDURE dbo.sp_ListConnections
    @DBUltra bit = 0,
    @PCUltra bit = 0,
    @DBIntra varchar(8000) = NULL,
    @DBExtra varchar(8000) = NULL,
    @PCIntra varchar(100)  = NULL,
    @PCExtra varchar(100)  = NULL,
    @DBTrain    char(1)    = NULL,
    @PCTrain varchar(2000) = NULL
AS

SET NOCOUNT ON

DECLARE @Return int
DECLARE @Retain int
DECLARE @Status int

SET @Status = 0

DECLARE @Task varchar(4000)

DECLARE @Work varchar(2000)

DECLARE @Host varchar(200)

DECLARE @SPID smallint

DECLARE @SPOT smallint

SET @SPOT = CASE WHEN CHARINDEX('2000 - 8',@@VERSION,1) > 0 THEN 50 ELSE 12 END

SET @Work = REPLACE(@PCTrain,CHAR(94),CHAR(39))

IF @PCTrain IS NULL

    BEGIN

       SELECT P.spid AS [ID]
            , RTRIM(P.hostname) AS [Client]
            , RTRIM(P.loginame) AS [Login]
            , RTRIM(P.program_name) AS [Application]
            , RTRIM(O.name) AS [Database]
            , P.open_tran AS [Tran]
            , P.blocked AS [Block]
            , STR(P.waittime/1000.0,5) AS [Wait]
            , CASE P.waittype
              WHEN 0x0000 THEN ''
              WHEN 0x0001 THEN 'Lock: Schema S'
              WHEN 0x0002 THEN 'Lock: Schema M'
              WHEN 0x0003 THEN 'Lock: S'
              WHEN 0x0004 THEN 'Lock: U'
              WHEN 0x0005 THEN 'Lock: X'
              WHEN 0x0006 THEN 'Lock: IS'
              WHEN 0x0007 THEN 'Lock: IU'
              WHEN 0x0008 THEN 'Lock: IX'
              WHEN 0x0009 THEN 'Lock: SIU'
              WHEN 0x000A THEN 'Lock: SIX'
              WHEN 0x000B THEN 'Lock: UIX'
              WHEN 0x000C THEN 'Lock: BU'
              WHEN 0x000D THEN 'Lock: RangeS S'
              WHEN 0x000E THEN 'Lock: RangeS U'
              WHEN 0x000F THEN 'Lock: RangeI N'
              WHEN 0x0010 THEN 'Lock: RangeI S'
              WHEN 0x0011 THEN 'Lock: RangeI U'
              WHEN 0x0012 THEN 'Lock: RangeI X'
              WHEN 0x0013 THEN 'Lock: RangeX S'
              WHEN 0x0014 THEN 'Lock: RangeX U'
              WHEN 0x0015 THEN 'Lock: RangeX X'
              WHEN 0x0041 THEN 'DTC'
              WHEN 0x0042 THEN 'OLEDB Provider'
              WHEN 0x0081 THEN 'Writelog'
              WHEN 0x0208 THEN 'CX Packet List'
              WHEN 0x020A THEN 'Shutdown'
              WHEN 0x020B THEN 'WAITFOR'
              WHEN 0x0400 THEN 'Latch NL'
              WHEN 0x0401 THEN 'Latch KP'
              WHEN 0x0402 THEN 'Latch SH'
              WHEN 0x0403 THEN 'Latch UP'
              WHEN 0x0404 THEN 'Latch EX'
              WHEN 0x0405 THEN 'Latch DT'
              WHEN 0x0410 THEN 'PageLatch NL'
              WHEN 0x0411 THEN 'PageLatch KP'
              WHEN 0x0412 THEN 'PageLatch SH'
              WHEN 0x0413 THEN 'PageLatch UP'
              WHEN 0x0414 THEN 'PageLatch EX'
              WHEN 0x0415 THEN 'PageLatch DT'
              WHEN 0x0420 THEN 'PageIOLatch NL'
              WHEN 0x0421 THEN 'PageIOLatch KP'
              WHEN 0x0422 THEN 'PageIOLatch SH'
              WHEN 0x0423 THEN 'PageIOLatch UP'
              WHEN 0x0424 THEN 'PageIOLatch EX'
              WHEN 0x0425 THEN 'PageIOLatch DT'
              WHEN 0x0800 THEN 'Network IO'
                          ELSE 'System Task' END AS [Type]
            ,   RTRIM(P.status) AS [Status]
            ,    LEFT(P.cmd,08) AS [Command]
            , CONVERT(varchar(20),P.last_batch,20) AS [Submission]
            , CONVERT(varchar(20),P.login_time,20) AS [Connection]
         FROM master.dbo.sysprocesses AS P
         JOIN master.dbo.sysdatabases AS O
           ON P.dbid = O.dbid
    LEFT JOIN master.dbo.sysprocesses AS Z
           ON P.spid = Z.blocked
        WHERE P.spid > @SPOT
          AND (@DBIntra IS NULL OR CHARINDEX('|'+CASE @DBTrain WHEN 'D' THEN RTRIM(O.name) WHEN 'A' THEN RTRIM(P.program_name) WHEN 'L' THEN RTRIM(P.loginame) ELSE RTRIM(P.hostname) END+'|','|'+(@DBIntra)+'|') > 0)
          AND (@DBExtra IS NULL OR CHARINDEX('|'+CASE @DBTrain WHEN 'D' THEN RTRIM(O.name) WHEN 'A' THEN RTRIM(P.program_name) WHEN 'L' THEN RTRIM(P.loginame) ELSE RTRIM(P.hostname) END+'|','|'+(@DBExtra)+'|') = 0)
          AND (@PCIntra IS NULL OR               CASE @DBTrain WHEN 'D' THEN RTRIM(O.name) WHEN 'A' THEN RTRIM(P.program_name) WHEN 'L' THEN RTRIM(P.loginame) ELSE RTRIM(P.hostname) END     LIKE @PCIntra)
          AND (@PCExtra IS NULL OR               CASE @DBTrain WHEN 'D' THEN RTRIM(O.name) WHEN 'A' THEN RTRIM(P.program_name) WHEN 'L' THEN RTRIM(P.loginame) ELSE RTRIM(P.hostname) END NOT LIKE @PCExtra)
          AND CASE
              WHEN @DBUltra = 0
              THEN 1
              WHEN P.blocked != 0
              THEN 1
              WHEN Z.spid IS NOT NULL
              THEN 1
              ELSE 0 END > 0
          AND CASE
              WHEN @PCUltra = 0
              THEN 1
              WHEN P.spid != @@PROCID AND RTRIM(P.status) != 'sleeping'
              THEN 1
              WHEN P.spid != @@PROCID AND  LEFT(P.cmd,08) != 'AWAITING'
              THEN 1
              ELSE 0 END > 0
     ORDER BY [Client],[Login],[Application],[Database],[Connection],[Submission]

    SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

    END

ELSE

    BEGIN

    IF ISNUMERIC(@PCTrain) <> 0

        BEGIN

        SELECT @Work = description FROM master.dbo.sysmessages WHERE error = CONVERT(int,@PCTrain)

        SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

        END

    IF CHARINDEX('@@SPID',@Work) = 0

        BEGIN

          DECLARE Items CURSOR FAST_FORWARD FOR
           SELECT RTRIM(P.hostname)
             FROM master.dbo.sysprocesses AS P
             JOIN master.dbo.sysdatabases AS O
               ON P.dbid = O.dbid
        LEFT JOIN master.dbo.sysprocesses AS Z
               ON P.spid = Z.blocked
            WHERE P.spid > @SPOT
              AND (@DBIntra IS NULL OR CHARINDEX('|'+CASE @DBTrain WHEN 'D' THEN RTRIM(O.name) WHEN 'A' THEN RTRIM(P.program_name) WHEN 'L' THEN RTRIM(P.loginame) ELSE RTRIM(P.hostname) END+'|','|'+(@DBIntra)+'|') > 0)
              AND (@DBExtra IS NULL OR CHARINDEX('|'+CASE @DBTrain WHEN 'D' THEN RTRIM(O.name) WHEN 'A' THEN RTRIM(P.program_name) WHEN 'L' THEN RTRIM(P.loginame) ELSE RTRIM(P.hostname) END+'|','|'+(@DBExtra)+'|') = 0)
              AND (@PCIntra IS NULL OR               CASE @DBTrain WHEN 'D' THEN RTRIM(O.name) WHEN 'A' THEN RTRIM(P.program_name) WHEN 'L' THEN RTRIM(P.loginame) ELSE RTRIM(P.hostname) END     LIKE @PCIntra)
              AND (@PCExtra IS NULL OR               CASE @DBTrain WHEN 'D' THEN RTRIM(O.name) WHEN 'A' THEN RTRIM(P.program_name) WHEN 'L' THEN RTRIM(P.loginame) ELSE RTRIM(P.hostname) END NOT LIKE @PCExtra)
              AND CASE
                  WHEN @DBUltra = 0
                  THEN 1
                  WHEN P.blocked != 0
                  THEN 1
                  WHEN Z.spid IS NOT NULL
                  THEN 1
                  ELSE 0 END > 0
              AND CASE
                  WHEN @PCUltra = 0
                  THEN 1
                  WHEN P.spid != @@PROCID AND RTRIM(P.status) != 'sleeping'
                  THEN 1
                  WHEN P.spid != @@PROCID AND  LEFT(P.cmd,08) != 'AWAITING'
                  THEN 1
                  ELSE 0 END > 0
         GROUP BY RTRIM(P.hostname)
         ORDER BY RTRIM(P.hostname)

        SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

        OPEN Items

        SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

        FETCH NEXT FROM Items INTO @Host

        SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

        WHILE @@FETCH_STATUS = 0 AND @Status = 0

            BEGIN

            SET @Task = 'NET SEND ' + @Host + CHAR(32) + @Work

            EXECUTE @Return = master.dbo.xp_cmdshell @Task, NO_OUTPUT

            SET @Retain = @@ERROR

            IF @Status = 0 SET @Status = @Retain
            IF @Status = 0 SET @Status = @Return

            FETCH NEXT FROM Items INTO @Host

            SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

            END

        CLOSE Items DEALLOCATE Items

        END

    ELSE

        BEGIN

          DECLARE Items CURSOR FAST_FORWARD FOR
           SELECT P.spid
             FROM master.dbo.sysprocesses AS P
             JOIN master.dbo.sysdatabases AS O
               ON P.dbid = O.dbid
        LEFT JOIN master.dbo.sysprocesses AS Z
               ON P.spid = Z.blocked
            WHERE P.spid > @SPOT
              AND (@DBIntra IS NULL OR CHARINDEX('|'+CASE @DBTrain WHEN 'D' THEN RTRIM(O.name) WHEN 'A' THEN RTRIM(P.program_name) WHEN 'L' THEN RTRIM(P.loginame) ELSE RTRIM(P.hostname) END+'|','|'+(@DBIntra)+'|') > 0)
              AND (@DBExtra IS NULL OR CHARINDEX('|'+CASE @DBTrain WHEN 'D' THEN RTRIM(O.name) WHEN 'A' THEN RTRIM(P.program_name) WHEN 'L' THEN RTRIM(P.loginame) ELSE RTRIM(P.hostname) END+'|','|'+(@DBExtra)+'|') = 0)
              AND (@PCIntra IS NULL OR               CASE @DBTrain WHEN 'D' THEN RTRIM(O.name) WHEN 'A' THEN RTRIM(P.program_name) WHEN 'L' THEN RTRIM(P.loginame) ELSE RTRIM(P.hostname) END     LIKE @PCIntra)
              AND (@PCExtra IS NULL OR               CASE @DBTrain WHEN 'D' THEN RTRIM(O.name) WHEN 'A' THEN RTRIM(P.program_name) WHEN 'L' THEN RTRIM(P.loginame) ELSE RTRIM(P.hostname) END NOT LIKE @PCExtra)
              AND CASE
                  WHEN @DBUltra = 0
                  THEN 1
                  WHEN P.blocked != 0
                  THEN 1
                  WHEN Z.spid IS NOT NULL
                  THEN 1
                  ELSE 0 END > 0
              AND CASE
                  WHEN @PCUltra = 0
                  THEN 1
                  WHEN P.spid != @@PROCID AND RTRIM(P.status) != 'sleeping'
                  THEN 1
                  WHEN P.spid != @@PROCID AND  LEFT(P.cmd,08) != 'AWAITING'
                  THEN 1
                  ELSE 0 END > 0
         GROUP BY P.spid
         ORDER BY P.spid

        SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

        OPEN Items

        SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

        FETCH NEXT FROM Items INTO @SPID

        SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

        WHILE @@FETCH_STATUS = 0 AND @Status = 0

            BEGIN

            SET @Task = REPLACE(@Work,'@@SPID',CONVERT(varchar(5),@SPID))

            EXECUTE (@Task)

            SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

            FETCH NEXT FROM Items INTO @SPID

            SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

            END

        CLOSE Items DEALLOCATE Items

        END

    END

IF @Status = 1 OR @Status NOT BETWEEN 0 AND 50000 RAISERROR ('Windows error [%d]',16,1,@Status)

SET NOCOUNT OFF

RETURN (@Status)

GO

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