0

Continuing from this post - I have some more questions I hope someone can help me with:

  1. Is there a way to select a specific database and/or table to get the queries from or add this as a column?

  2. In my queries there are some variables shown as @P1 or @GUID. Is there a way to get the data that was inserted there?

I am only using Express to I also have no access to SQL Profiler.

Community
  • 1
  • 1
Meln Rabes
  • 19
  • possible duplicate of [How do I view executed queries within SQL Server Management Studio?](http://stackoverflow.com/questions/2449634/how-do-i-view-executed-queries-within-sql-server-management-studio) – Abe Miessler Feb 22 '12 at 15:26
  • In your previous question, @Mike Mooney seems to answer this. – Abe Miessler Feb 22 '12 at 15:26

2 Answers2

1

sys.dm_exec_sql_text has a dbid column, so you can filter on that. For example I took the query from the other answer and added a where clause filtering on queries against master:

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE dest.dbid = DB_ID('master')
ORDER BY deqs.last_execution_time DESC

Note that not all queries have the right database context (or a database context at all). For example, if you have a query that joins two tables that are in different databases, you will only see one dbid - it will either be the executing context and may or may not be one of the databases referenced in the query. So applying the filter might actually hide queries you are interested in.

You may be able to obtain parameters by digging into the XML from other DMOs such as sys.dm_exec_cached_plans and sys.dm_exec_query_plan. If you have an execution plan already for a query that you've captured, it is going to be much easier to use a tool like SQL Sentry Plan Explorer than to wade through gobs of XML yourself.

Disclaimer: I work for SQL Sentry, who provides the free tool to the community.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Thanks Aaron.dbid is NULL in most cases so I filtered on the table names that I could find. Will have a look at the tool as that looks the easiest one and to XML if it doesn't list the vars. I also found a cursor state that might be involved so looking in to that to. – Meln Rabes Feb 27 '12 at 11:50
0

Just an FYI, you know that even though SQL Express doesn't include profile, if you have access to it you can use.

Diego
  • 34,802
  • 21
  • 91
  • 134
  • 1
    And that is useful for the OP to know because of what? I quote... `I also have no access to SQL Profiler` – MatBailie Feb 22 '12 at 15:37
  • because he said on the other post "it looks like Profiler isn't included in Express." He might not know that even though it is not included, it may be used – Diego Feb 22 '12 at 15:51
  • Don't have access to it right now Diego but I'll keep it in mind. – Meln Rabes Feb 27 '12 at 11:55