方法一：
SELECT * FROM
[Master].[dbo].[SYSPROCESSES] WHERE [DBID]
IN
(
SELECT
[DBID]
FROM
[Master].[dbo].[SYSDATABASES]
WHERE
NAME='escm2'
)
方法二： 执行 SP_WHO 或者 SP_WHO2
sp_who [ [ **@loginame = ] 'login' | session ID | 'ACTIVE' **]
结果：
background：会话正在运行一个后台任务，例如死锁检测。
rollback： 会话具有正在处理的事务回滚。
pending： 会话正在等待工作线程变为可用。
runnable： 会话的任务在等待获取时间量程时位于计划程序的可运行队列中。
spinloop： 会话的任务正在等待调节锁变为可用。
suspended： 会话正在等待事件（如 I/O）完成。
方法三：根据SessionId查询
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
如何查询 who_sp存储过程的结果？
- 通过临时表
CREATE TABLE #sp_who2
(
SPID INT,
Status VARCHAR(255),
Login VARCHAR(255),HostName VARCHAR(255),
BlkBy VARCHAR(255),DBName VARCHAR(255),
Command VARCHAR(255),CPUTime INT,
DiskIO INT,LastBatch VARCHAR(255),
ProgramName VARCHAR(255),SPID2 INT,
REQUESTID INT);
INSERT INTO #sp_who2 EXEC sp_who2
SELECT *
FROM #sp_who2
-- Add any filtering of the results here :
WHERE DBName = 'escm2'
-- Add any sorting of the results here :
ORDER BY DBName ASC
DROP TABLE #sp_who2
- 还是临时表
declare @tempTable table (SPID INT,Status VARCHAR(255),
Login VARCHAR(255),HostName VARCHAR(255),
BlkBy VARCHAR(255),DBName VARCHAR(255),
Command VARCHAR(255),CPUTime INT,
DiskIO INT,LastBatch VARCHAR(255),
ProgramName VARCHAR(255),SPID2 INT,
REQUESTID INT);
INSERT INTO @tempTable
EXEC sp_who2
select *
from @tempTable
where DBName = 'escm2'
查询当前服务用户执行的SQL的语句
-- 查询SQL语句
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
ORDER BY
er.blocking_session_id DESC
