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
Hive Explode List from Json-String
Postgresql Error: Syntax Error At or Near "Varchar"
Convert Varchar Mmddyyyy to Mm/Dd/Yyyy Datetime and Select the Most Recent Date Only
How to Escape Back Slash in SQL Server
Select Multiple Columns Count One Column and Group by One Column in One Table
Joining Tables from Different Servers
How to Exclude Specific Row from Query in MySQL
How to Find the Row Count for All Your Tables in Postgres
How to Retrieve Microseconds or Milliseconds from MySQL Current Time
What Is and How to Remove Tablespace Error from My Database
Regex Pattern Inside SQL Replace Function
Find Out Where MySQL Is Installed on MAC Os X
Check If a Row Exists, Otherwise Insert
Sql Merge Two Rows With Same Id But Different Column Values (Oracle)
Mysql: Error Code: 1054. Unknown Column in 'Where Clause'
Sql Server-How to Replace a Date Column With a Current Date When the Column Has a Null Value