2

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.

Fuzz Evans
  • 2,893
  • 11
  • 44
  • 63
  • Are you using SQL Server Profiler? I think that it has the functionality that you're looking for: https://learn.microsoft.com/en-us/sql/tools/sql-server-profiler/sql-server-profiler?view=sql-server-ver16 – akseli Jan 07 '23 at 21:58
  • @akseli - my understanding of the question is that the 500 queries were got from profiler but now they need to see which one of those returns a resultset with a keyword. Profiler won't show resultsets – Martin Smith Jan 07 '23 at 22:06
  • @MartinSmith - you are correct. I have used Profiler to intercept the queries from an application to my db, and from there, I need to associate the queries to their results. Which per your reply, and this post, doesn't sound like profiler itself can do unfortunately: stackoverflow.com/questions/2086713/can-sql-profiler-display-return-result-sets-alongside-the-query – Fuzz Evans Jan 09 '23 at 17:43
  • 1
    I should have been clearer - Your response is about SET STATISTICS PROFILE ON, and I added a comment to that as well. When I said 'per your reply', it was in reference to your comment on the question, stating profiler can't accomplish this. – Fuzz Evans Jan 09 '23 at 20:24

2 Answers2

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.

enter image description here

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • This does correctly include the query that the results belong to - I wish it was beautified or structured more for readability - but I can at least piece it together from what it gives. Thank you. – Fuzz Evans Jan 09 '23 at 18:10
1

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.

enter image description here

Fuzz Evans
  • 2,893
  • 11
  • 44
  • 63