use master
go
IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_who_ex'), 'IsProcedure') = 1
BEGIN
    DROP PROCEDURE dbo.sp_who_ex
    IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_who_ex'), 'IsProcedure') = 1
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_who_ex >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.sp_who_ex >>>'
END
go
create procedure sp_who_ex (
        @spid int = 0,
        @dbname nvarchar(30) = N'%',
        @loginame nvarchar(256) = N'%',
        @hostname nvarchar(256) = N'%',
        @programname nvarchar(256) = N'%',
        @ntusername nvarchar(256) = N'%',
        @onlyblocked bit = 0,
        @orderby varchar(30) = 'spid',
        @report varchar(10) = NULL
)
-- with encryption
as
/********************************************************************************/
/* Created By   : Umachandar Jayachandran (UC)                                  */
/* Created On   : 20 January 1999                                               */
/* Description  : Written to avoid using sp_who or sp_who2 sps. Those sps are   */
/*                not very flexible & sp_who2 hangs if tempdb itself is locked. */
/*                This SP also provides more filters to use.                    */
/********************************************************************************/
/*      Resources  :    https://umachandar.com/resources.htm                 */
/********************************************************************************/
if (@dbname <> N'%') and (db_id(@dbname) is null)
begin
        raiserror ('Invalid database name specified.', 1, 2)
        return(-1)
end
set @orderby = lower(coalesce(@orderby, 'spid'))
if (@orderby not in ('spid', 'cpu', 'cpu desc', 'io', 'io desc', 'host', 'program',
                        'ntuser', 'sqllogin', 'lastbatch desc', 'lastbatch'))
begin
        raiserror ('Invalid order by option specified.', 1, 2)
        return(-1)
end
set @report = lower(@report)
if @report not in ('users') and @report is not null
begin
        raiserror ('Invalid report option specified.', 1, 2)
        return(-1)
end
if @report = 'users'
begin
        select convert(varchar(30), nt_username) as "NT User Name",
                        convert(varchar(50), loginame) as "SQL Login Name",
                        count(*) as "Number of Connections"
        from master..sysprocesses
        group by nt_username, loginame
        order by 3 desc, 2, 1
        compute sum(count(*)), count(convert(varchar(50), loginame))
        return(0)
end
if @onlyblocked = 1 and
                exists(select * from master..sysprocesses where blocked > 0)
        select spid as "Blocking Process ID",
                convert(varchar(15), case dbid when 0 then 'no database context'
                                        else db_name(dbid)
                                     end) as "Database",
                cmd as "Current Command",
                convert(varchar(20), status) as "Process ID Status",
                blocked as "Blocking Process ID",
                cpu as "CPU Time",
                physical_io "DISK I/O",
                last_batch as "Last Batch",
                lastwaittype as "Last Waittype",
                waittime as "Current Waittime",
                convert(varchar(30), waitresource) as "Wait Resource",
                convert(varchar(15), hostname) as "Host Name",
                convert(varchar(30), program_name) as "Program Name",
                convert(varchar(20), nt_username) as "NT User Name",
                convert(varchar(30), loginame) as "SQL Login Name"
        from master..sysprocesses p1 
        where exists(select * from master..sysprocesses p2
                        where p2.blocked = p1.spid)

select spid as "Process ID",
        convert(varchar(15), case dbid when 0 then 'no database context'
                                else db_name(dbid)
                             end) as "Database",
        cmd as "Current Command",
        convert(varchar(20), status) as "Process ID Status",
        blocked as "Blocking Process ID",
        cpu as "CPU Time",
        physical_io "DISK I/O",
        last_batch as "Last Batch",
        lastwaittype as "Last Waittype",
        waittime as "Current Waittime",
        convert(varchar(30), waitresource) as "Wait Resource",
        convert(varchar(15), hostname) as "Host Name",
        convert(varchar(30), program_name) as "Program Name",
        convert(varchar(20), nt_username) as "NT User Name",
        convert(varchar(30), loginame) as "SQL Login Name"
from master..sysprocesses
where nt_username like @ntusername + '%' and 
        program_name like @programname + '%' and 
        hostname like @hostname + '%' and 
        loginame like @loginame + '%' and 
        spid = (case @spid when  0 then spid else @spid end) and
        dbid = (case when @dbname = '%' then dbid else db_id(@dbname) end) and
        ((blocked > 0 and @onlyblocked = 1) or (@onlyblocked = 0))
order by case @orderby
                when 'spid' then spid
                when 'cpu' then cpu
                when 'cpu desc' then -1*cpu
                when 'io' then physical_io
                when 'io desc' then -1*physical_io
                when 'host' then 0
                when 'program' then 0
                when 'ntuser' then 0
                when 'sqllogin' then 0
                when 'lastbatch desc' then -1*cast(last_batch as float) 
         end,
        case @orderby
                when 'spid' then ''
                when 'cpu' then ''
                when 'cpu desc' then ''
                when 'io' then ''
                when 'io desc' then ''
                when 'host' then hostname
                when 'program' then program_name
                when 'ntuser' then nt_username
                when 'sqllogin' then loginame
                when 'lastbatch' then convert(varchar, last_batch, 121)
         end
go
IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_who_ex'), 'IsProcedure') = 1
BEGIN
    GRANT EXECUTE ON dbo.sp_who_ex To Public
    PRINT '<<< CREATED PROCEDURE dbo.sp_who_ex >>>'
END
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_who_ex >>>'
go

This page was last updated on May 01, 2006 04:28 PM.