CODEWITHSUNDEEP
sqlsql-serversql-server-2008ssms
Meln Rabes
Meln Rabes

Reputation: 19

View specific executed queries within SQL Server Management Studio?

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.

Upvotes: 0

Views: 1017

Answers (2)

Diego
Diego

Reputation: 36146

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

Upvotes: 0

anon
anon

Reputation:

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.

Upvotes: 1

Related Questions