Deleted stored procedure from MS SQL!

Do not panic, as there are 2 ways to retrieve your precious stored procedure:


1) Recover them from the dblog table

SELECT

[cp].[refcounts],

[cp].[usecounts],

[cp].[objtype],

[st].[dbid],

[st].[objectid],

[st].[text],

[qp].[query_plan]

FROM

sys.dm_exec_cached_plans cp

CROSS

APPLY sys.dm_exec_sql_text ( cp.plan_handle ) st

CROSS

APPLY sys.dm_exec_query_plan ( cp.plan_handle ) qp 

where [st].[text] like '%__AUDIT%'

Select Convert(varchar(Max),Substring([RowLog Contents 0]

,33

,LEN([RowLog Contents 0]))) as [Script]

from fn_dblog(NULL,NULL)

Where [Operation]='LOP_DELETE_ROWS' And [Context]='LCX_MARK_AS_GHOST'

And [AllocUnitName]='sys.sysobjvalues.clst'

2) Recover them from cached database plan

SELECT

[cp].[refcounts],

[cp].[usecounts],

[cp].[objtype],

[st].[dbid],

[st].[objectid],

[st].[text],

[qp].[query_plan]

FROM

sys.dm_exec_cached_plans cp

CROSS

APPLY sys.dm_exec_sql_text ( cp.plan_handle ) st

CROSS

APPLY sys.dm_exec_query_plan ( cp.plan_handle ) qp ;