sp_who System Stored Procedure

For SQL Server 6.5 information, see sp_who in What's New for SQL Server 6.5.

Reports information about current SQL Server users and processes.

Syntax

sp_who [login_id | 'spid']

where

login_id

Is the user's login ID on SQL Server. If no name is specified, the procedure reports on all active users of SQL Server.

spid

Specifies a specific process. You can supply a process number as a parameter if you enclose it in quotation marks (SQL Server expects a char type).

Remarks

This system stored procedure reports information about a specified user, a specified SQL Server process, or all users currently running a process on SQL Server. Executing sp_who without parameters reports which users are running what processes in all databases.

Examples

A.    List All Current Processes

This example shows how to view all current processes by executing sp_who without parameters. The blk column contains the system process ID of a blocking process; this value references the spid column of this table (self-referencing). A blocking process (which may have an exclusive lock) is one that is holding resources that another process needs. In this example, process 11 (a SELECT on a table) is blocked by process 10 (an uncommitted transaction).

sp_who
go
spid   status     loginame hostname  blk   dbname     cmd              
------ ---------- -------- --------- ----- ---------- ---------------- 
1      sleeping   sa                 0     master     MIRROR HANDLER   
2      sleeping   sa                 0     master     LAZY WRITER      
3      sleeping   sa                 0     master     CHECKPOINT SLEEP 
4      sleeping   sa                 0     master     RA MANAGER       
10     sleeping   Mary     WKSTA1    0     pubs       AWAITING COMMAND 
11     runnable   Joe      WKSTA5    10    pubs       SELECT           

B.    List a Specific User's Process

This example shows how to view information about a single current user by loginame or spid.

sp_who Mary

Or

sp_who '10'

Permission

Execute permission defaults to the public group.

Table Used

master.dbo.sysprocesses

See Also