ALTER VIEW dbo.Session AS SELECT CAST(SP.spid AS INTEGER) AS [Connection ID], CAST(RTRIM(SP.loginame) AS NVARCHAR(64)) COLLATE Latin1_General_CS_AS AS [User ID], CAST(CASE WHEN SP.[spid] = @@SPID THEN 1 ELSE 0 END AS TINYINT) AS [My Session], CONVERT(DATETIME, '1754-01-01 ' + CONVERT(CHAR(8), SP.login_time, 108), 120) AS [Login Time], CONVERT(DATETIME, CONVERT(CHAR(10), SP.login_time, 120) + ' 00:00:00:000', 121) AS [Login Date], CAST(SD.name AS NVARCHAR(128)) COLLATE Latin1_General_CS_AS AS [Database Name], CAST(RTRIM(SP.program_name) AS NVARCHAR(64)) COLLATE Latin1_General_CS_AS AS [Application Name], CASE WHEN SP.[nt_domain] <> '' THEN 1 ELSE 0 END AS [Login Type], CAST(RTRIM(SP.hostname) AS NVARCHAR(64)) COLLATE Latin1_General_CS_AS AS [Host Name], SP.cpu AS [CPU Time (ms)], CASE WHEN SP.[memusage] < 0 THEN 0 ELSE SP.[memusage] * 8 END AS [Memory Usage (KB)], SP.physical_io AS [Physical I_O], CAST(CASE WHEN SP.[blocked] <> 0 THEN 1 ELSE 0 END AS TINYINT) AS Blocked, CAST(CASE WHEN SP.[blocked] <> 0 THEN SP.[waittime] ELSE 0 END AS INT) AS [Wait Time (ms)], CAST(SP.blocked AS INTEGER) AS [Blocking Connection ID], CAST(ISNULL(RTRIM(SPB.loginame), '') AS NVARCHAR(64)) COLLATE Latin1_General_CS_AS AS [Blocking User ID], CAST(ISNULL(RTRIM(SPB.hostname), '') AS NVARCHAR(64)) COLLATE Latin1_General_CS_AS AS [Blocking Host Name], CAST(ISNULL(OBJECT_NAME(SPL.rsc_objid), '') AS NVARCHAR(64)) COLLATE Latin1_General_CS_AS AS [Blocking Object] FROM master.dbo.sysprocesses AS SP INNER JOIN master.dbo.sysdatabases AS SD ON SP.dbid = SD.dbid LEFT OUTER JOIN master.dbo.sysprocesses AS SPB ON SP.blocked = SPB.spid LEFT OUTER JOIN master.dbo.syslockinfo AS SPL ON SP.spid = SPL.req_spid AND SP.dbid = SPL.rsc_dbid AND SPL.req_spid <> 0 AND SPL.rsc_objid <> 0 AND SPL.req_status = 3 WHERE (SP.ecid = 0)