Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Every time I create a query and run it to create a report I save the query in my Projects folder in case I have to rerun the query in the future or if I have to modify it for any reason but it seems that for some reason a query which I ran sometime in mid June was not saved. I need to see the code I used in the query because I am looking at the report and some numbers do not look right so I need to check the query to see if there was something written incorrectly in the code.

Is there anyway in some logs maybe where I might be able to see the code for the query I ran (and did not save) back in June? This would be AWSOME if I can. Thanks!

share|improve this question
add comment

2 Answers 2

up vote 6 down vote accepted

If your SQL Server has not been restarted there may a chance that your query text might have have been cached along with its execution plan. You may want to run the following to see if query text is still in the plan.

SELECT 
    cp.objtype AS ObjectType,
    OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
    cp.usecounts AS ExecutionCount,
    st.TEXT AS QueryText,
    qp.query_plan AS QueryPlan
FROM 
    sys.dm_exec_cached_plans AS cp 
CROSS APPLY 
    sys.dm_exec_query_plan(cp.plan_handle) AS qp 
CROSS APPLY 
    sys.dm_exec_sql_text(cp.plan_handle) AS st 
WHERE 
    st.text LIKE  '%query search criteria%' 
share|improve this answer
    
Thanks. I'm not sure if it has gotten reset but this code will come in handy! –  JuanVelez Jul 6 '12 at 19:31
add comment

Database transaction logs only contain the physical operations on the database, not the queries that are executed.

There are several ways to capture queries running against a SQL Server instance (server-side trace, Profiler trace, Extended Events), but they had to have been running while the query was executed. The only chance to retrieve it is to look in the plan cache as another answer mentions.


SSMS Tools Pack allows you to automatically saves scripts in SSMS as they are run. This is probably your best bet, as you don't really care about the other stuff that something like a server-side trace would get you.

share|improve this answer
    
Thanks! Really Appreciate it! I'll check it out. –  JuanVelez Jul 6 '12 at 19:31
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.