Can anyone point me to a good SQL sniffer where I can watch the SQL commands that run against my database in real time?
If it's free, that would be great.
Use a SQL Trace, and if you are looking for a GUI to go along with it you can use SQL Profiler. It ships with all versions of SQL Server except SQL Server Express. SQL Trace is utilize directly through T-SQL and is available even with SQL Server Express, though a pretty good working knowledge of SQL Trace is desirable as it can be a little daunting to set up without the GUI.
What you would do is select the events that pertain to SQL statements/batches/stored procedures executing. You will be able to capture the actual text statement(s).
If you don't want to wrestle with selecting out of the available (and many) events, you can use a built-in template. It sounds like you'd probably want to use the T-SQL template, but look through it before just blindly running the trace.
At the botton of that linked reference, there are a handful of "tasks" (tutorials) to get you started in the right direction.
EDIT: Aaron brings up a good point (see comments). Utilizing SQL Server Profiler is typically used for a quick glimpse or relatively short trace to do some snap troubleshooting. There are definitely performance impacts, and I have seen busy servers brought to their knees because of it. If you are looking for a longer, less performance impact trace I'd look to use T-SQL to generate the server-side traces. Another great alternative is Extended Events.