In SQL Server, getting the current SQL statements that are being executed by the active sessions is quite easy. In prior versions, one either had to make use Enterprise Manager or had to use DBCC INPUTBUFFER (<spid>) in order to get that information. In newer versions, one can just make use of the ::fn_get_sql() function. This was first introduced in SQL Server 2000 and is much better than the DBCC command since there are no text limitations.
Using the cross apply operator that we had discussed in one of our previous blog posts, one can write up a simple script to show all the SQL commands that are being fired by the active sessions on an instance:
SELECT spid, db_name(s.dbid), text
FROM master.dbo.sysprocesses as s
cross apply ::fn_get_sql(s.sql_handle)