1 IF OBJECT_ID('TempDB..#sp_who') is NOT NULL
2 BEGIN
3 DROP TABLE #sp_who
4 END
5 CREATE TABLE #sp_who
6 (SPID INT,
7 Status VARCHAR(20),
8 Login VARCHAR(20),
9 HostName VARCHAR(50),
10 BlkBy VARCHAR(20),
11 DBName VARCHAR(50),
12 CommAND VARCHAR(255),
13 CPUTime VARCHAR(255),
14 diskIO VARCHAR(255),
15 LastBatch VARCHAR(20),
16 ProgramName VARCHAR(255),
17 SPID1 VARCHAR(255),
18 RequestID VARCHAR(255))
19
20 INSERT INTO #sp_who
21 EXECUTE sp_who2
22
23
24
25 SELECT GETDATE() AS Runtime,
26 c.Status,c.CommAND,A.*,
27 db_name(B.dbid) AS DBNAME
28
29 FROM
30 (SELECT es.session_id,es.login_name,OBJECT_NAME(est.ObjectID,est.DBID) AS ObjectName,es.host_name,est.text
31 ,cn.last_read,cn.last_write,es.program_name
32 FROM sys.dm_exec_sessions es
33 INNER JOIN sys.dm_tran_session_transactions st
34 ON es.session_id=st.session_id
35 INNER JOIN sys.dm_exec_connections cn
36 ON es.session_id=cn.session_id
37 CROSS APPLY sys.dm_exec_sql_text(cn.most_recent_sql_handle)est
38 LEFT OUTER JOIN sys.dm_exec_requests er
39 ON st.session_id=er.session_id
40 AND er.session_id IS NULL
41 ) a
42 INNER JOIN sys.sysprocesses B
43 ON A.session_id=B.spid AND B.loginame<>''
44 join #sp_who as c
45 on b.spid=c.SPID
46 where c.login<>'sa'
47 order by a.login_name
