查看当前的连接和锁

本文来自:http://www.sqlservercentral.com/scripts/sp_who2/67351/

这个存储过程使用xsp_cmdshell得到IP地址,它取决于进程的数量而影响执行快慢

用法如下:exec usp_GetConnectionInfo

Create Proc usp_GetConnectionInfo
AS
Begin
    Declare @spid int,@ClientIPAddress varchar(500),@cmd varchar(260),@HostName varchar(100),@sqltext varchar(max)
    Declare @Lock_Info Table
        ( spid int,dbid int,objid int,indid int,locktype varchar(20),
            Resource varchar(100),Mode varchar(15),lockstatus varchar(100)
        )

    Declare @Process_Info Table
        ( spid int,proc_status varchar(10),Login_user varchar(100),HostName varchar(100),BlkBy char(10),DBname varchar(100),command varchar(200),
            CPUtime    int,diskio int,lastbatch varchar(100),programname varchar(250),spid_1 int,requestid int
        )
        
    Declare @All_Info Table
        ( spid int,Sql_Statement varchar(max),HostName varchar(50),HostIP varchar(100),Dbname varchar(100),ObjName varchar(100),Index_Type varchar(50),
            Lock_Type varchar(10),Lock_Mode varchar(10),Lock_Status varchar(50),BlockedBy char(10),Cputime int,
            DiskIO int,programname varchar(100),lastbatch varchar(100)
        )

    Insert into @Lock_Info
    Exec sp_lock
        
    Insert into @Process_Info    
    Exec sp_who2
    
    Declare mycur CURSOR
    FOR
    select spid,hostname from @Process_Info
    
    Declare @Results TABLE
     (
        Results varchar(500)
     )        
    
    create table #sqlstatement(eventtype varchar(100),parameters int,sqlstatement varchar(max))
    
        
    OPEN mycur
        fetch next from mycur into @spid,@HostName
        
        while(@@fetch_status=0)
        
        BEGIN
            insert into #sqlstatement
            EXEC ('DBCC Inputbuffer (' + @spid + ')')
            select @sqltext=sqlstatement from #sqlstatement
            truncate table #sqlstatement
            SET @cmd = 'ping ' + @HostName
            INSERT INTO @Results
            execute master..xp_cmdshell @cmd
            SELECT @ClientIPAddress=Replace(Left(Results, CharIndex(']', Results)), 'Pinging ', '')
            FROM @Results    WHERE Results LIKE 'Pinging%'
            
            Insert into @All_Info
            select p.spid,@sqltext,@hostname,@ClientIPAddress,p.dbname,object_Name(l.objid),    
            CASE indid When 0 Then 'HEAP'
                     When 1 Then 'CLUSTERED'
                     Else 'NON-CLUSTERED'
            END,l.locktype,l.Mode,l.lockstatus,p.blkby,p.CPUtime,p.diskio,p.programname,p.lastbatch    
            from @Lock_Info l right join @Process_Info p on l.spid=p.spid where p.spid=@spid
            and p.hostname not like '%.%'    
            fetch next from mycur into @spid,@HostName
    END

drop table #sqlstatement
Close mycur
deallocate mycur
select * from @All_Info
END
--sp_configure 'xp_cmdshell',1
--reconfigure