This is a query to view all current activity on the database server (assuming you have the proper permissions). You can see who is connected, if they have any open transactions, if they are blocked or blocking, and what exactly they are executing.
This is similar to the SQL Activity view in Spotlight but can be more useful if you need to apply advanced filtering. You can even filter for specific text inside the user queries, for example if you needed to see everyone using a particular table.
select sp.spid, sp.[status], sp.blocked, sdb.name as [Database], sp.loginame, sp.cpu, sp.physical_io, sp.login_time, sp.last_batch, sp.open_tran, sp.hostname, sp.[program_name], sp.cmd, qt.[text] as QueryText from sysprocesses sp inner join sys.databases sdb on sdb.database_id = sp.dbid cross apply sys.dm_exec_sql_text(SP.sql_handle) qt order by sdb.name asc, sp.spid asc;