How to See Query History in SQL Server Management Studio

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.

SQL Server Management Studio - history of commands run

You can use the modify_date column in sys.objects table

SELECT *
FROM SYS.OBJECTS
WHERE Modify_Date BETWEEN <date_of_modification> AND <date_of_modification> + 1

and then you can try to narrow it down.

You can be even more specific and run the query for just tables and stored procedures.

SELECT *
FROM SYS.objects
WHERE TYPE IN ('IT', 'S', 'U', 'P', 'PC', 'X')
AND modify_date BETWEEN '10-Jun-2014' AND '11-Jun-2014'

Recover unsaved SQL query scripts

Posting this in case if somebody stumbles into same problem.

Googled for Retrieve unsaved Scripts and found a solution.

Run the following select script. It provides a list of scripts and its time of execution in the last 24 hours. This will be helpful to retrieve the scripts, if we close our query window in SQL Server management studio without saving the script. It works for all executed scripts not only a view or procedure.

Use <database>
SELECT execquery.last_execution_time AS [Date Time], execsql.text AS [Script] FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
ORDER BY execquery.last_execution_time DESC


Related Topics



Leave a reply



Submit