SQL Server Profiler:
- File → New Trace
- The “General” Tab is displayed.
- Here you can choose “Save to file:” so its logged to a file.
- View the “Event Selection” Tab
- Select the items you want to log.
- TSQL → SQL:BatchStarting will get you sql selects
- Stored Procedures → RPC:Completed will get you Stored Procedures.
More information from Microsoft: SQL Server 2008 Books Online – Using SQL Server Profiler
Update – SQL Express Edition:
A comment was made that MS SQL Server Profiler is not available for the express edition.
There does appear to be a free alternative: Profiler for Microsoft SQL Server 2005 Express Edition