Search code examples
sqlsql-serversql-server-2005dynamic-management-views

Join multiple Dynamic Management View tables

I'm trying to get a list of all the sessions with the login name, status and the SQL query if they are currently running a query. The below query works fine but only shows people who are currently running a query.

How can I display them all even if the session is asleep? I think I have to change the type of join but I'm not sure which one.

SELECT c.session_id,
   s.login_name,
   s.status AS SessionStatus,
   r.status AS RequestStatus,
   st.text
 FROM sys.dm_exec_connections c
 INNER JOIN sys.dm_exec_sessions s
 ON c.session_id = s.session_id
 LEFT JOIN sys.dm_exec_requests r
 ON c.session_id = r.session_id
 CROSS APPLY 
 sys.dm_exec_sql_text(r.sql_handle) AS st
Solution

  • The CROSS APPLY is the problem. It essentially turns your LEFT JOIN into an inner join. Change it to an OUTER APPLY.