June 14, 2010 at 5:20 pm
Hello all,
I just did this sp_who and I think some of you will find it useful, You can provide it with three parameter ( it is shielded against code injection too...)
1) spid
2) databasename
3) session status
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_who3] Script Date: 06/14/2010 16:17:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_who3] (
@SessionID int = NULL ,
@DBID varchar(100) = NULL ,
@statusID varchar(100) = NULL
)
AS
BEGIN
DECLARE @sSql nvarchar(4000)
DECLARE @sWhereClause nvarchar(4000)
DECLARE @sFootClause nvarchar(4000)
DECLARE @ParmDefinition nvarchar(4000)
DECLARE @NewLine nvarchar(4000)
SET @NewLine = CHAR(13) + CHAR(10)
SET @sWhereClause = '' -- Initialise
SET @sSql = 'SELECT ' + @NewLine
+ ' SPID = e.spid, ' + @NewLine
+ ' Status = max(A.status), ' + @NewLine
+ ' waits = sum(e.waittime), ' + @NewLine
+ ' [Login] = max(A.login_name), ' + @NewLine
+ ' HostName = max(A.host_name), ' + @NewLine
+ ' BlkBy = max(c.blocking_session_id), ' + @NewLine
+ ' DBName = max(DB_Name(e.dbid)), ' + @NewLine
+ ' Command = max(e.cmd), ' + @NewLine
+ ' SQLStatement = max(isnull(substring(b.text,1,4000),'''')), ' + @NewLine
+ ' ObjectName = max(OBJECT_NAME(b.objectid)), ' + @NewLine
+ ' Programname = max(A.Program_name), ' + @NewLine
+ ' ElapsedMS = max(C.total_elapsed_time), ' + @NewLine
+ ' CPUTime = sum(isnull(e.cpu,0)), ' + @NewLine
+ ' IOReads = max(isnull(a.logical_reads + a.reads,0)), ' + @NewLine
+ ' IOWrites = max(isnull(a.writes,0)), ' + @NewLine
+ ' LastWaitType = max(e.lastwaittype), ' + @NewLine
+ ' LastBatch = max(e.last_batch), ' + @NewLine
+ ' DiskIO = sum(e.physical_io) ' + @NewLine
+ ' FROM sys.dm_exec_sessions (nolock) a ' + @NewLine
+ ' LEFT JOIN sys.dm_exec_requests (nolock) c ON c.session_id = a.session_id ' + @NewLine
+ ' LEFT JOIN sys.sysprocesses E (nolock) ON e.spid = a.session_id ' + @NewLine
+ ' OUTER APPLY sys.dm_exec_sql_text(e.sql_handle) b ' + @NewLine
+ ' LEFT JOIN sys.dm_exec_connections (nolock) d ON d.session_id = a.session_id ' + @NewLine
+ ' where a.session_id > 50 '+ @NewLine
if @SessionID IS NOT NULL
SET @sWhereClause= @sWhereClause+@NewLine+ ' AND a.session_id = @SessionID'
IF @DBID IS NOT NULL
SET @sWhereClause= @sWhereClause+@NewLine+ ' AND DB_Name(e.dbid) = @DBID'
IF @StatusID IS NOT NULL
SET @sWhereClause= @sWhereClause+@NewLine+ ' AND a.status = @StatusID'
SET @sFootClause = ' group by e.spid ORDER BY e.spid'
SET @ParmDefinition = '@SessionID int,' + @NewLine
+ ' @DBID varchar(100),' + @NewLine
+ ' @StatusID varchar(100)'
SET @sSql = @sSql + @sWhereClause+@newLine + @sFootClause
EXEC sp_executesql @sSql,@ParmDefinition,
@SessionID=@SessionID,
@DBID=@DBID,
@StatusID=@StatusID
END
Regards
June 14, 2011 at 6:20 am
doesn't work on case-sensitive servers
June 15, 2011 at 7:26 am
no need to reinvent the wheel. use sp_whoisactive, an absolutely amazing freebie from Adam Machanic. See sqlblog.com for code (which is documented) and 30 different blog posts detailing it's power.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 15, 2011 at 8:53 am
mmm sp_whoisactive is quite nice, but my last version of sp_who3 ( the one posted here is one year old) has some more useful options
June 16, 2011 at 7:06 am
Can you please share with us what those "more useful options" might be please?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply