There are multiple solutions. As noted by @squillman, you can view it in SQL Server via Profiler. But just within CF, you have others choices.
- You can turn on CF's debugging output to view query info at the end of the request. That has pros and cons, of course. If in development, it's generally good. In prod, best to avoid it (even if you limit the IP addresses that can view debug output, as many assert that even otehr users still pay the cost of the debugging being generated).
- You can also log the info yourself, within code. Since CF7, there's been a new RESULT attribute, which hold a struct with data about the query, including the SQL statement. You could write that out with CFLOG, CFTRACE, and other tags (or related script functions).
- You can also have CF log it itself. A little-known feature was added in CF8, a new checkbox within the "advanced settings" for a datasource definition (in the CF Admin) lets you "log activity". Be careful, it's quite verbose.
- You can also use built-in monitoring features within the various CF Server Monitors, whether the free one built into CF Enterprise (or Developer, but not Standard), or the 3rd party ones, FusionReactor and SeeFusion. The CFSM focuses only on slow queries, though. FR and SF can show you every query within a given request (or all queries on the server, including those not associated with any query, like the client variable purge, and so on.) One think I like about FR in particular is that it logs every query (if you enable query logging), whereas SF again only logs slow queries. (Each can be changed to the other alternative.)
- Finally, all those tools do their query logging by way of wrapping the JDBC drivers. You can also do it yourself, manually, without those tools, using any of many jdbc wrapper alternatives. Just google jdbc wrapper and you'll find many.
I also talk about all these options in more detail in a blog entry I did, which while it focused on spying on ORM queries within CF, it all applies just as well to any queries that CF generates (and on any CFML engine): Spying on ORM database interactions: Hibernate, Transfer, etc. on any CFML engine.
Finally, I'll note that as for using SQL Server Profiler, note that it too has its plusses and minuses (it can generate far more details about query available as options, including even the execution plan graph per query, which is great, but it also like any monitoring has some risk of overhead depending on how things are configured. Many resources exist addressing that concern and solutions.
Hope that's helpful.