USE [master] GO /****** Object: StoredProcedure [dbo].[sp_SystemBlockedUsersW] Script Date: 07/22/2011 15:11:25 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_SystemBlockedUsersW] AS BEGIN SELECT t1.spid , t1.status, loginame = RTRIM(t1.loginame), hostname = LEFT(RTRIM(t1.hostname), 20), program_name = RTRIM(t1.program_name), t1.blocked, t1.dbid, dbname = RTRIM((CASE WHEN t1.dbid = 0 THEN NULL WHEN t1.dbid <> 0 THEN db_name(t1.dbid) END)), RTRIM(t1.nt_username)nt_username, RTRIM(t1.cmd)cmd, datediff(MINUTE, t1.last_batch, GETDATE()) waittime_dk, SUBSTRING(sql.text, stmt_start / 2, CASE WHEN stmt_end<1 THEN 8000 ELSE (stmt_end-stmt_start)/ 2 END) AS RunningSqlText, sql.text AS FullSqlText, t1.cpu, SUBSTRING( CONVERT(VARCHAR, t1.last_batch, 111) , 6 , 5 ) + ' ' + SUBSTRING( CONVERT(VARCHAR, t1.last_batch, 113) , 13 , 8 ) AS 'last_batch_time', t1.waittime waittime, t1.lastwaittype FROM master.dbo.sysprocesses (NOLOCK) t1 CROSS apply sys.dm_exec_sql_text(t1.sql_handle) AS SQL WHERE t1.blocked <> 0 OR t1.spid IN ( SELECT t2.blocked FROM master.dbo.sysprocesses (NOLOCK) t2) ORDER BY t1.dbid DESC, t1.spid END
