sys.sysprocesses

Microsoft recommends to query the information that is provided in sys.sysprocesses through the newer views

spid

spid returns a process' session id. The session id can be used in the kill statement to terminate a process:

kill 52;

The process that corresponds to the current session can be queried with where spid = @@spid.

Get a process' SQL statement text

sys.sysprocesses can be joined to sys.dm_exec_sql_text to get the SQL statement for the respective process:

select
   prc.spid,
   prc.login_time,
   prc.last_batch,
   right(convert(varchar, 
            dateadd (ms,
            datediff(ms, prc.last_batch, getdate()),
           '1900-01-01'), 
            121), 12)                                 last_batch_,
   db_name(prc.dbid)                                  db_name    ,
   suser_sname(prc.sid)                               sid_       , 
   prc.nt_username,
   prc.loginame,                                                   -- Note: it's loginame, not loginname.
   prc.hostname,
   prc.program_name,
   sql.text                                           sql_text
from
   sys.sysprocesses     prc                 cross apply
   sys.dm_exec_sql_text(prc.sql_handle) sql
where
--
-- Exclude own session:
   prc.spid <> @@spid                                  and
-- -------------------------------------------------------
   prc.cmd   not in (
      'AWAITING COMMAND',
      'MIRROR HANDLER'  ,
      'LAZY WRITER'     ,
      'CHECKPOINT SLEEP',
      'RA MANAGER'
   )                                                   and
   prc.status not in (
      'background',
      'sleeping'
   );

Github repository about-MSSQL, path: /administration/schemas/sys/objects/views/sysprocesses/sql-text.sql

sys.sysprocesses

spid

Get a process' SQL statement text

See also