SP_WHO to see SQL statement

Quick query to see user and full SQLstatement.  Don't forget one can use the Profile from SSMS for a full view of all db activity.


SELECT  D.text SQLStatement, A.Session_ID SPID, ISNULL(B.status,A.status) Status,
A.login_name Login, A.host_name HostName, C.BlkBy,  DB_NAME(B.Database_ID) DBName,
B.command, ISNULL(B.cpu_time, A.cpu_time) CPUTime, ISNULL((B.reads + B.writes),
(A.reads + A.writes)) DiskIO,  A.last_request_start_time LastBatch, A.program_name FROM
   sys.dm_exec_sessions A    LEFT JOIN    sys.dm_exec_requests B  
 ON A.session_id = B.session_id   LEFT JOIN    
 (        SELECT                 A.request_session_id SPID,        
       B.blocking_session_id BlkBy        
  FROM sys.dm_tran_locks as A          
 INNER JOIN sys.dm_os_waiting_tasks as B        
  ON A.lock_owner_address = B.resource_address        ) C
  ON A.Session_ID = C.SPID   OUTER APPLY sys.dm_exec_sql_text(sql_handle) D

Rank Over

,RANK() OVER
    (PARTITION BY col] ORDER BY [colA] desc, [colB] desc) AS Rank

from MSN  (http://msdn.microsoft.com/en-us/library/ms176102.aspx):

USE AdventureWorks2012;
GO
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
    ,RANK() OVER 
    (PARTITION BY i.LocationID ORDER BY i.Quantity DESC) AS Rank
FROM Production.ProductInventory AS i 
INNER JOIN Production.Product AS p 
    ON i.ProductID = p.ProductID
WHERE i.LocationID BETWEEN 3 AND 4
ORDER BY i.LocationID;
GO