查看进程

 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