viewing recent executions in SQL Server

1. 2005 and above, using DMV to view current and recent executions.

select * from sys.dm_exec_connections c
cross apply sys.dm_exec_sql_text(c.most_recent_sql_handle) q

dbcc inputbuffer(sql handle)

select s.text,r.* from sys.dm_exec_requests r
cross apply sys.dm_exec_text(r.sql_handle) s

SELECT spid, db_name(s.dbid), text

FROM master.dbo.sysprocesses as s

cross apply fn_get_sql(s.sql_handle)

basic idea is to use sys.dm_exec_sql_text and dbcc inputbuffer, fn_get_sql to view the content.

2. 2000, use dbcc inputbeffer and fn_get_sql. use cursor to loop each of the sql_handle in sysprocesses, or retrieve the information in two separate steps.