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  :	http://www.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