Monthly Archives: April 2009

Sometimes I want to start with clean information. I can always add back in the owner and identifiers.

DECLARE @vcTableName varchar(128)
SET @vcTableName = 'dbo.test table'
SET @vcTableName = 'mytable.test'
SET @vcTableName = QUOTENAME('mytable.test table')
IF CHARINDEX('[', @vcTableName) > 0
BEGIN
SET @vcTableName = REPLACE(REPLACE(@vcTableName, '[', ''), ']', '')
END
IF CHARINDEX('.', @vcTableName) > 0
BEGIN
SET @vcTableName = SUBSTRING(@vcTableName, CHARINDEX('.', @vcTableName) + 1, LEN(@vcTableName) - CHARINDEX('.', @vcTableName))
END
PRINT '***' + @vcTableName + '***'
PRINT 'dbo.' + QUOTENAME(@vcTableName)

Here' my helper function which I put in my master_admin database.

CREATE  FUNCTION fn_GetCurrentSQL (

   @sql_handle binary(20),

   @stmt_start INT,

   @stmt_end INT

)

RETURNS VARCHAR(8000)

AS

BEGIN

   RETURN (

   SELECT 

       SUBSTRING(s.TEXT,

           COALESCE(NULLIF((@stmt_start/2)+1, 0), 1),

           CASE @stmt_end 

               WHEN -1 

                   THEN DATALENGTH(s.TEXT) 

               ELSE 

                   (@stmt_end - @stmt_start) 

               END

           ) 

   FROM    ::fn_get_sql(@sql_handle) s

)

END

GO

Here’s my sp_who2 replacement script.

CREATE PROCEDURE [dbo].[sp__who2] (
 @nSortFlag INT = 0,
 @nMinAgo INT = NULL,
 @nSPID INT = NULL,
 @vcDBName VARCHAR(128) = NULL,
 @bAll BIT = 0
 )
AS /*
** Procedure

** Description

** Parameters

** Requirements

** Usage
EXEC dbo.sp_who2 0, 0
EXEC dbo.sp__who2 0, 0, 84
EXEC dbo.sp__who2 0, NULL, 84
EXEC dbo.sp__who2 NULL, NULL, NULL
EXEC dbo.sp__who2 0, NULL, NULL
EXEC dbo.sp__who2 1, NULL, NULL
EXEC dbo.sp__who2 2, NULL, NULL
EXEC dbo.sp__who2 0, 2, NULL
EXEC dbo.sp__who2 1, 2, NULL


-- DECLARE @nSortFlag bit,
-- @nMinAgo int
--
-- SELECT
-- @nSortFlag = 0,
-- @nMinAgo = 1

** Changes
 2008-02-19 John Perry

*/

DECLARE @bootjobtime DATETIME
SELECT @bootjobtime = MIN(last_batch)
FROM master.dbo.sysprocesses (NOLOCK)
WHERE spid < 20


SELECT CAST('A' AS VARCHAR(50)) SortC,
 CAST(0 AS INT) SortN,
 IDENTITY( INT, 1,1 ) AS ID_Num,
 CAST(DB_NAME(dbid) AS VARCHAR(35)) DatabaseName,
 spid,
 ecid,
 BlkBy = CASE CONVERT(CHAR(5), ISNULL(blocked, '0'))
 WHEN '0' THEN ' .'
 ELSE CONVERT(CHAR(5), ISNULL(blocked, '0'))
 END,
 open_tran AS openx,
 physical_io AS io,
 cpu,
 memusage,
 waittime AS waitMS,
 CAST(ISNULL(master_admin.dbo.fn_GetCurrentSQL(sql_handle, stmt_start, stmt_end), cmd) AS VARCHAR(1024)) cmd,
 hostname,
 program_name,
 CONVERT(SYSNAME, RTRIM(loginame)) AS loginname,
 CONVERT(VARCHAR, last_batch, 121) AS last_date,
 CASE WHEN waittype = 0x0000 THEN lastwaittype
 ELSE '*' + lastwaittype
 END WaitType,
 waitresource
INTO #tmpWHO
FROM master.dbo.sysprocesses (NOLOCK)
WHERE (ISNULL(blocked, '0') > 0
 OR open_tran > 0
 )
 AND (@nSortFlag IS NOT NULL)
ORDER BY spid,
 ecid


INSERT #tmpWHO
 (SortC,
 SortN,
 DatabaseName,
 spid,
 ecid,
 BlkBy,
 openx,
 io,
 cpu,
 memusage,
 waitMS,
 cmd,
 hostname,
 program_name,
 loginname,
 last_date,
 WaitType,
 waitresource
 )
 SELECT CASE WHEN 1 = @nSortFlag THEN 'B' + CONVERT(VARCHAR(35), DB_NAME(dbid))
 WHEN 2 = @nSortFlag THEN 'B' + CONVERT(VARCHAR(35), last_batch, 121)
 ELSE 'B'
 END SortC,
 CASE WHEN 0 = 0 THEN spid
 ELSE 99
 END SortN,
 CAST(DB_NAME(dbid) AS VARCHAR(35)) DatabaseName,
 spid,
 ecid,
 BlkBy = CASE CONVERT(CHAR(5), ISNULL(blocked, '0'))
 WHEN '0' THEN ' .'
 ELSE CONVERT(CHAR(5), ISNULL(blocked, '0'))
 END,
 open_tran AS openx,
 physical_io AS io,
 cpu,
 memusage,
 waittime AS waitMS,
 cmd,
 hostname,
 program_name,
 CONVERT(SYSNAME, RTRIM(loginame)) AS loginname,
 CONVERT(VARCHAR, last_batch, 121) AS last_date,
 CASE WHEN waittype = 0x0000 THEN lastwaittype
 ELSE '*' + lastwaittype -- (active)
 END WaitType,
 waitresource
 FROM master.dbo.sysprocesses (NOLOCK)
 WHERE ((@nSortFlag IS NULL)
 OR (ISNULL(blocked, '0') = 0
 AND open_tran = 0
 )
 )
 AND (@nSPID > 0
 OR @nMinAgo IS NULL
 OR last_batch >= DATEADD(minute, -@nMinAgo, GETDATE())
 )

SELECT DatabaseName,
 spid,
 ecid,
 BlkBy,
 openx,
 io,
 cpu,
 memusage,
 waitMS,
 WaitType,
 cmd,
 hostname,
 program_name,
 loginname,
 last_date,
 waitresource
 --, SortC,SortN,id_NUM
FROM #tmpWHO
WHERE (@bAll = 1
 OR last_date > @bootjobtime
 OR @nMinAgo IS NULL
 )
 AND (@vcDBName IS NULL
 OR DatabaseName LIKE '%' + @vcDBName + '%'
 )
 AND (@nSPID IS NULL
 OR @nSPID = 0
 OR spid = @nSPID
 )
ORDER BY SortC,
 SortN,
 spid,
 ecid

DROP TABLE #tmpWHO;
GO