How can I include my query in my query results?

Question

I have a SQL Trace that I'm reading through that has ~500 queries in it.

I am executing all of the queries against my target db with an output to a text file so I can search for my keyword in the results.

I have found the keyword/result one of the queries, but it's difficult to tell which query it belongs to since it just shows column headers.

Is there a way to include the query that ran as part of the query results, or some other method that would help indicate which query it belongs to?

In SSMS Query Settings there's an option for Include the query in the result set', however that compiles all 500 queries at the top of the output, and not interlaced with their corresponding results.

Answer 1

One way would be to SET STATISTICS PROFILE ON and then run your queries.

When you find the result set that has the value you care about look at the StmtText in the next statistics profile output.

There are some caveats with the approach though.

• You will need SHOWPLAN permissions
• Very Simple statements that don't produce an execution plan (like SELECT 1) won't produce any statistics profile output.
• The StmtText from stored procedures will be from the SQL statements inside the proc - not the EXEC call.

But hopefully it will provide sufficient detail that you can navigate around these issues if you do encounter them.

Answer 2

I'm leaving Martin's answer as correct, since it did answer the question I posed with native functionality. I don't love how the output looked, so I built a tool to achieve this as well.

Shameless self plug: https://sourceforge.net/projects/sql-trace-interlace/ In case anyone else runs into this in the future.