For details about Transact-SQL, see sp_who, sp_who2, and sp_who3.

Transact-SQL reference   Sp_who, sp_who2 and sp_who3  

Sp_who can return the following information: (the optional parameter loginname, or active indicates the number of active sessions)

Spid (system process ID)

Status)

Loginame (user login name)

Hostname (User host name)

BLK (spid of the blocked process)

Dbname (the process is usingDatabaseName)

CMD (the command type currently being executed)

In addition to the output information of sp_who, sp_who2 also displays the following information: (optional parameter: loginname, or active indicates the number of active sessions)

Cputime (total CPU time occupied by processes)

Diskio (total number of disk reads by a process)

Lastbatch (the time when the customer last called the stored procedure or executed the query)

Programname (application used to initialize the connection)ProgramName, or host name)

Sp_who3 is a user-defined Stored Procedure (optional parameter spid). It displays the details of "non-system session, Active session.

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475

Create procedure sp_who3 (@ sessionid Int = NULL) as begin select spid = ER. session_id, status = SES. status, [Login] = SES. login_name, host = SES. host_name, blkby = ER. blocking_session_id, dbname = db_name (ER. database_id), commandtype = ER. command, sqlstatement = ST. text, objectname = object_name (St. objectid), elapsedms = ER. total_elapsed_time, cputime = ER. cpu_time, ioreads = ER. logical_reads + er. reads, iowrites = ER. writes, lastwaittype = ER. last_wait_type, starttime = ER. start_time, protocol = con.net _ transport, connectionwrites = con. num_writes, connectionreads = con. num_reads, clientaddress = con. client_net_address, authentication = con. auth_scheme from sys. dm_exec_requests er outer apply sys. dm_exec_ SQL _text (ER. SQL _handle) ST left join sys. dm_exec_sessions ses on SES. session_id = ER. session_id left join sys. dm_exec_connections con on Con. session_id = SES. session_id where er. session_id> 50 and @ sessionid is null or ER. session_id = @ sessionid order by er. blocking_session_id DESC, er. session_id end go

conversion from: http://www.cnblogs.com/xiurui12345/archive/2012/05/15/2501453.html