Last executed queries for a specific database
This works for me to find queries on any database in the instance. I'm sysadmin on the instance (check your privileges):
SELECT deqs.last_execution_time AS [Time], dest.text AS [Query], dest.*
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('msdb')
ORDER BY deqs.last_execution_time DESC
This is the same answer that Aaron Bertrand provided but it wasn't placed in an answer.
TSQL: Get Last Queries Ran
Yes, take a look, this will give you the 50 most recent executed SQL statements
sql 2005 and up only
SELECT TOP 50 * FROM(SELECT COALESCE(OBJECT_NAME(s2.objectid),'Ad-Hoc') AS ProcName,
execution_count,s2.objectid,
(SELECT TOP 1 SUBSTRING(s2.TEXT,statement_start_offset / 2+1 ,
( (CASE WHEN statement_end_offset = -1
THEN (LEN(CONVERT(NVARCHAR(MAX),s2.TEXT)) * 2)
ELSE statement_end_offset END)- statement_start_offset) / 2+1)) AS sql_statement,
last_execution_time
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 ) x
WHERE sql_statement NOT like 'SELECT TOP 50 * FROM(SELECT %'
--and OBJECTPROPERTYEX(x.objectid,'IsProcedure') = 1
ORDER BY last_execution_time DESC
Obtain SQL Server executed query history of specific database
You can achieve it in this way
Where dbid = (Select database_id from sys.databases Where name = 'your_database_name')
Full query:
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 dbid = (select database_id from sys.databases Where name = 'your_database_name')
ORDER BY deqs.last_execution_time DESC
How do I view executed queries within SQL Server Management Studio?
Use SQL Profiler and use a filter on it to get the most expensive queries.
get all executed queries in sql server
It is better to use the Profiler for this task.
But if you want a query in SQL Server 2005+
you can use system views sys.dm_exec_query_stats, which returns aggregate performance statistics for cached query plans
but only for the cashed plans, and sys.dm_exec_sql_text. Unfortunately they don't have information about session ID so you can get only cashed queries of a server instance.
set transaction isolation level read uncommitted
select top 1000
creation_time,
last_execution_time,
case
when sql_handle IS NULL then ' '
else(substring(st.text,(qs.statement_start_offset+2)/2,(
case
when qs.statement_end_offset =-1 then len(convert(nvarchar(MAX),st.text))*2
else qs.statement_end_offset
end - qs.statement_start_offset)/2 ))
end as query_text,
db_name(st.dbid)as db_name,
object_schema_name(st.objectid, st.dbid)+'.'+object_name(st.objectid, st.dbid) as object_name
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(sql_handle) st
ORDER BY db_name, object_name
Related Topics
Oracle: How to Implement a "Natural" Order-By in a SQL Query
Differences Between "Foreign Key" and "Constraint Foreign Key"
How to Find the Number of Occurrences of a Particular Character in a String Using SQL
How to Create a "Unique" Constraint on a Boolean MySQL Column
Count Null Values from Multiple Columns with SQL
Return Rows from Insert with on Conflict Without Needing to Update
How to Concatenate All Strings from a Certain Column for Each Group
Trying to Sum Distinct Values SQL
How to Perform a "Like" Statement in a Ssis Expression
Sql: Many-To-Many Table and Query
Split String into Several Rows
Anyway for Ado to Read Updated Data from a Read-Only Excel File Before Save? (Vba)
Does Inner Join Performance Depends on Order of Tables
Stop MySQL Reusing Auto_Increment Ids
SQL Server Regular Expressions
Split a Single Column of Data with Comma Delimiters into Multiple Columns in Ssis