/* Jobs Details for a specific db (works on 2000 and 2005)
**
** Charlie (19/02/2008)
** Modified (11/06/2008)
*/

-- ==== Configuration =========================================================

DECLARE @dbName VARCHAR(50)            SET @dbName = '<your dbname here>'

-- ==== ------------- =========================================================

USE MASTER
SET NOCOUNT ON

IF object_Id('tempdb..#spwho') IS NOT NULL DROP TABLE #spwho
IF object_Id('tempdb..#info') IS NOT NULL DROP TABLE #info
IF object_Id('tempdb..#dbccInput') IS NOT NULL DROP TABLE #dbccInput

DECLARE
	@spid INT
	, @sqlHandle BINARY(20)
	, @sqlVersion INT

-- Which version of SQL server are we running.
SET @sqlVersion = CASE
	WHEN @@VERSION LIKE '%2005 - 9.%' THEN 2005
	WHEN @@VERSION LIKE '%2000 - 8.%' THEN 2000
END

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
	)

-- Add a column if it's 2005
IF @sqlVersion = 2005 ALTER TABLE #spWho ADD [REQUESTID] INT NOT NULL DEFAULT 0

CREATE TABLE #dbccInput (
	eventType NVARCHAR(30)
	, paramaters INT
	, eventInfo NVARCHAR(4000)
	)

CREATE TABLE #info (
	[spid] INT NOT NULL
	, [status] VARCHAR(20) NOT NULL
	, [login] VARCHAR(255) NOT NULL
	, [blkBy] VARCHAR(10) NOT NULL
	, [command] VARCHAR(255) NOT NULL
	, [CPUTime] INT NOT NULL
	, [DiskIO] INT NOT NULL
	, [LastBatch] VARCHAR (255) NOT NULL
	, [ProgramName] VARCHAR (255) NOT NULL
	, [sqlHandle] BINARY(20) NULL
	, [eventInfo] NVARCHAR(4000) NULL
	, [sql] TEXT NULL
	)

INSERT INTO #spWho EXEC sp_who2

-- Get basic job info for the database we are intersted in
INSERT INTO #info (
	[spid]
	, [status]
	, [login]
	, [blkBy]
	, [command]
	, [CPUTime]
	, [DiskIO]
	, [LastBatch]
	, [ProgramName]
	, [sqlHandle]
	)
SELECT
	sw.[spid]
	, sw.[status]
	, sw.[login]
	, sw.[blkBy]
	, sw.[command]
	, sw.[CPUTime]
	, sw.[diskIO]
	, sw.[lastBatch]
	, sw.[programName]
	, sp.[sql_handle]
FROM
	#spWho sw
	JOIN master.dbo.sysprocesses sp ON sp.[spid] = sw.[spid]
WHERE
	[DBName] = @dbName

-- Get the sql info for each spid
DECLARE sqlCursor CURSOR LOCAL READ_ONLY FOR
SELECT
	i.[spid]
	, i.[sqlHandle]
FROM
	#info i

OPEN sqlCursor

	FETCH NEXT FROM sqlCursor INTO
		@spid
		, @sqlHandle

	WHILE (@@fetch_status = 0) BEGIN

		INSERT INTO #dbccInput
		EXEC ('DBCC INPUTBUFFER(' + @spid + ')')

		UPDATE #info
		SET
			[eventInfo] = dbccI.[eventInfo]
		FROM
			#dbccInput dbccI
		WHERE
			[spid] = @spid
		
		-- clear the input info
		TRUNCATE TABLE #dbccInput

		-- Get the sql full text (only does currently running process)
		UPDATE #info
		SET
			[sql] = fgs.[text]
		FROM
			::fn_get_sql(@sqlHandle) fgs
		WHERE
			[spid] = @spid

		FETCH NEXT FROM sqlCursor INTO
			@spid
			, @sqlHandle

	END

CLOSE sqlCursor
DEALLOCATE sqlCursor

-- Display the results
SELECT
	[spid]
	, [status]
	, [login]
	, [blkBy]
	, [command]
	, [CPUTime]
	, [DiskIO]
	, [LastBatch]
	, [ProgramName]
	, [eventInfo] AS lastEvent
	, [sql] AS currentSql
FROM
	#info
	
IF object_Id('tempdb..#spwho') IS NOT NULL DROP TABLE #spwho
IF object_Id('tempdb..#info') IS NOT NULL DROP TABLE #info
IF object_Id('tempdb..#dbccInput') IS NOT NULL DROP TABLE #dbccInput