You’re looking to run a SQL Server profiler trace. Performing a trace is Microsoft’s means of capturing every statement executed against an instance (or specified database(s)), but be aware that this level of information comes with a performance penalty inherent in capturing everything.

Now this is by no means the only way to get what you’re looking for. You could also write a script or a set of scripts leveraging a number of different T-SQL syntaxes/commands/functions to gather this information, although nothing guarantees you’ll see everything like profiler does. Here are three options:

1. For SQL Server 2005 you can use the sys.dm_exec_requests DMV and CROSS APPLY the sys.dm_exec_sql_text function in a loop. The MSDN link for sys.dm_exec_sql_text includes a few sample scripts to illustrate how you can leverage that interaction.
2. For SQL Server 2000 (and 2005 if you’d like) you can use the fn_get_sql function together with the sys.sysprocesses system view to see SQL Sytnaxes and to correlate them with current session information.
3. Using DBCC INPUTBUFFER together with sysprocesses (or sys.dm_exec_requests) in a loop can provide similar information to fn_get_sql.

Note that both fn_get_sql and DBCC INPUTBUFFER are limited by the amount of text that can be returned, so don’t consider these an end-all-be-all solution for SQL monitoring.

If you don’t want to get into writing all the scripts you’ll need, or you don’t have the time and resources to create a process that will maintain the databases, tables, reports, and other related monitoring data there is certainly no shortage of third-party applications vying for your attention.  These products specialize in providing user-friendly, formatted, guided answers and advice to important questions like:

• How long does that statement usually run?
• Who usually runs that statement?
• Has the plan for that statement changed? If so, when and why and who changed it?
• …and the list goes on and on.

Once you start asking those questions, SQLServerPedia can certainly be your resource to get answers, and Quest has an arsenal of tools to help analyze, diagnose and resolve even your most complicated performance or management issues.  Hope to see you back here soon!