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
转载于:https://www.cnblogs.com/Julia007/p/10038433.html
