DB Stuff: Beyond sp_who

--This is a query designed to return all processes in all dbs on one server.



--declare a table to hold the results. allows for filtering and sorting

 DECLARE @TempTbl TABLE (DBName nVarchar(300), session_id INT, login_time nVarchar(300), host_name nVarchar(300), program_name nVarchar(300), host_process_id nVarchar(300), client_version nVarchar(300), client_interface_name nVarchar(300), security_id nVarchar(300), login_name nVarchar(300), nt_domain nVarchar(300), nt_user_name nVarchar(300), status nVarchar(300), context_info nVarchar(300), cpu_time nVarchar(300), memory_usage nVarchar(300), total_scheduled_time nVarchar(300), total_elapsed_time nVarchar(300), endpoint_id nVarchar(300), last_request_start_time nVarchar(300), last_request_end_time nVarchar(300), reads nVarchar(300), writes nVarchar(300), logical_reads nVarchar(300), is_user_process nVarchar(300), text_size nVarchar(300), language nVarchar(300), date_format nVarchar(300), date_first nVarchar(300), quoted_identifier nVarchar(300), arithabort nVarchar(300), ansi_null_dflt_on nVarchar(300), ansi_defaults nVarchar(300), ansi_warnings nVarchar(300), ansi_padding nVarchar(300), ansi_nulls nVarchar(300), concat_null_yields_null nVarchar(300), transaction_isolation_level nVarchar(300), lock_timeout nVarchar(300), deadlock_priority nVarchar(300), row_count nVarchar(300), prev_error nVarchar(300), original_security_id nVarchar(300), original_login_name nVarchar(300), last_successful_logon nVarchar(300), last_unsuccessful_logon nVarchar(300), unsuccessful_logons nVarchar(300), group_id INT)

INSERT INTO @TempTbl
SELECT DB_NAME(dbid),s.*
FROM sys.dm_exec_sessions s
LEFT OUTER JOIN sys.sysprocesses p
ON S.session_id = p.spid
WHERE dbid > 0SELECT *
FROM @TempTbl
WHERE
      login_name NOT IN ('sa', '<domain>\<server>', '<db_user>')
      AND DBName NOT IN ('master')
ORDER BY DBName, login_name

--This query is sp_who in a filterable fashion

DECLARE @WhoTbl TABLE
(SPID INT,[Status] nVarchar(300),
[Login] nVarchar(300),
HostName nVarchar(50),BlkBy varchar(300),DBName nVarchar(300),Command nVarchar(3000),CPUTime INT,DiskIO INT,LastBatch varchar(300),ProgramName nVarchar(300),SPID2 INT,REQUESTID INT)
INSERT INTO @WhoTbl
EXEC sp_who2

SELECT * FROM @WhoTbl
WHERE
DBName NOT IN ('master', 'msdb')
ORDER BY DBName