recovering a query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

recovering a query

Author

Topic

Sean_B
Posting Yak Master

111 Posts

Posted - 2009-08-26 : 10:14:40

Hi,

I accidentally closed a window without saving the stored procedure I was writing, I assume it has gone.

I had run a big chunk of it as a query.

Is it possible to get this back from a cache?

Sean

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-08-26 : 10:41:33

If you had run the query, you may be able to get it back if you run the following, replacing the text between <> with part of your query:


SELECT plan_handle, pvt.set_options, pvt.sql_handle, text
FROM (
    SELECT plan_handle, epa.attribute, epa.value, st.text 
    FROM sys.dm_exec_cached_plans 
        OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
        CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
    WHERE cacheobjtype = 'Compiled Plan' and st.text like '%<defining part of you query>%') AS ecpa 
PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN (set_options,sql_handle)) AS pvt;