Jul 14 2011 at 15:41

Расширение возможностей sp_who2 для MSSQL

Invite pending

Данный пост предназначен для тех, кто хочет немного расширить функционал для процедуры sp_who2 сервера MSSQL.
Чем меня не устраивал cстандартный sp_who2:

Нет возможности выбрать процессы по конкретной базе
Нет возможности выбрать процессы по конкретному юзеру
Нет возможности выбрать процессы по конкретному имени хоста
Нет возможности выбрать только заблокированные процессы и блокурующие процессы
Нет возможности посмотреть последнюю запущенную команду процесса

Как вы наверное догадались — всё это есть в моей процедуре.

Назовём её sp_who3.
На данный момент в списке параметров присутствует 4 необязательных параметра:

 @dbname varchar(200) - имя базы данных

 @user varchar(255) - логин пользователя

 @hostname varchar (255) - имя хоста

 @BlkBy bit - при установленом флаге - отображать только заблокированные и блокурующие процессы

Замечу, что я для поиска команды для процесса использовал DBCC. Но существуют и другие способы получить данный результат. Информацию можно найти здесь.
Ну вот собственно и всё.
Вылаживаю код. Надёюсь кому-то ещё пригодится. Удачи.

 
CREATE PROCEDURE [dbo].[sp_who4]
@dbname varchar(200) = '%',
@user varchar(255)='%',
@hostname varchar (255)='%',
@BlkBy bit=0
as


    create table #sp_who2
        (
        id int IDENTITY(1,1),
        SPID int null,
        STATUS varchar(1000) null,
        Login varchar(1000) null,
        HostName varchar(1000) null,
        BlkBy varchar(1000) null,
        DbName varchar(1000) null,
        Command varchar(1000) null,
        CPUTime int null,
        DISKIO int,
        LasTBatch varchar(1000) null,
        ProgramName varchar(1000) null,
        SPID2 int,
        REQUESTID int
    )
    insert into #sp_who2 
    (SPID, STATUS, Login, HostName, BlkBy, DbName, Command, CPUTime, DISKIO, LasTBatch, ProgramName, SPID2, REQUESTID)
    exec sp_who2
	

    declare @sp_id int
    declare @id int
    declare @sql varchar(8000)

    create table #sp_id_input(EventType varchar(255), [Parameters] varchar(255), EventInfo text, id int null)

    declare my_cursor cursor local for
        select id, SPID
        from #sp_who2
    for read only

    open my_cursor
    fetch next from my_cursor
    into @id, @sp_id

    while @@FETCH_STATUS=0
    begin
        set @sql = 'DBCC INPUTBUFFER ('+convert(varchar(10),@sp_id)+')'
		
        insert 
        into #sp_id_input 
        (EventType, [Parameters] , EventInfo)
        exec(@sql)

        update #sp_id_input 
        set id=@id 
        where isnull(id,-1)=-1

        fetch next from my_cursor
        into @id, @sp_id
    end

    close my_cursor
    deallocate my_cursor
	
    select SPID, STATUS, Login, HostName, BlkBy, DbName, Command, CPUTime, DISKIO, LasTBatch, ProgramName, SPID2, REQUESTID, EventType , [Parameters] , isnull(EventInfo,'') as EventInfo,
	        case 
		        when (select count(*) from #sp_who2 sp_1 where sp_1.BlkBy=convert(varchar(10),sp.SPID))>0 then 'blocking'
		        when len(rtrim(ltrim(replace(BlkBy,'.',' '))))>0 then 'blocked'
		        else 'no'
	        end as BlockStatus

    from #sp_who2 sp
    left join #sp_id_input inp on inp.id=sp.id
    where DbName  like @dbname 
        and [Login] like @user 
        and (
	        case @BlkBy 
		        when 0 then 1 
		        else len(rtrim(ltrim(replace(BlkBy,'.',' '))))+(select count(*) from #sp_who2 sp_1 where sp_1.BlkBy=convert(varchar(10),sp.SPID)) end
	        )>0
     drop table #sp_who2
     drop table #sp_id_input

Hubs:

Lumber room