/****** Object:  StoredProcedure [dbo].[CG_spWho]    Script Date: 01/28/2010 09:16:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CG_spWho]

	@dbName VARCHAR(255) = NULL
	, @targetTable VARCHAR(512) = NULL

AS BEGIN

	SET NOCOUNT ON

	IF OBJECT_ID('tempdb..#spwho') IS NOT NULL DROP TABLE #spwho

	CREATE TABLE #spWho (
		[SPID] INT NOT NULL
		, [Status] VARCHAR (255) NOT NULL
		, [Login] VARCHAR (255) NOT NULL
		, [HostName] VARCHAR (255) NOT NULL
		, [BlkBy] VARCHAR(10) NOT NULL
		, [DBName] VARCHAR (255) NULL
		, [Command] VARCHAR (255) NOT NULL
		, [CPUTime] INT NOT NULL
		, [DiskIO] INT NOT NULL
		, [LastBatch] VARCHAR (255) NOT NULL
		, [ProgramName] VARCHAR (255) NOT NULL
		, [SPID2] INT NOT NULL
		, [REQUESTID] INT NOT NULL DEFAULT 0
		)

	INSERT INTO #spWho EXEC sp_who2

	IF @targetTable IS NULL SELECT
		sw.[dbname]
		, sw.[spid]
		, sw.[status]
		, sw.[login]
		, sw.[blkBy]
		, sw.[command]
		, sw.[CPUTime]
		, sw.[diskIO]
		, sw.[lastBatch]
		, sw.[programName]
		, t.[text] AS [Sql Text]
	FROM
		#spWho sw
		JOIN master.dbo.sysprocesses sp ON sp.[spid] = sw.[spid]
		CROSS APPLY fn_get_sql(sp.[sql_handle]) t
	WHERE
		(
			[DBName] LIKE @dbName
			OR
			@dbName IS NULL
		)
	ORDER BY
		sw.[blkBy] DESC
		, sw.[dbName] ASC

	ELSE BEGIN
		DECLARE @sql NVARCHAR(MAX)
		SET @sql = N'

		INSERT INTO ' + @targetTable + '
		SELECT
			sw.[dbname]
			, sw.[spid]
			, sw.[status]
			, sw.[login]
			, sw.[blkBy]
			, sw.[command]
			, sw.[CPUTime]
			, sw.[diskIO]
			, sw.[lastBatch]
			, sw.[programName]
			, t.[text] AS [Sql Text]
		FROM
			#spWho sw
			JOIN master.dbo.sysprocesses sp ON sp.[spid] = sw.[spid]
			CROSS APPLY fn_get_sql(sp.[sql_handle]) t
		WHERE
			(
				[DBName] LIKE @dbName
				OR
				@dbName IS NULL
			)'

		EXEC sp_executeSql @sql, N'@dbName VARCHAR(255)', @dbName

	END

	IF OBJECT_ID('tempdb..#spwho') IS NOT NULL DROP TABLE #spwho
END