Recover a Deleted Stored Procedure

Posted on Updated on

To recover a deleted object, the SQL Service MUST NOT be restarted. If the SQL Service has been restarted then you won’t be able to find your deleted Stored Procedure.

SELECT A.objtype, B.text, C.query_plan
FROM sys.dm_exec_cached_plans A
CROSS APPLY sys.dm_exec_sql_text ( A.plan_handle ) B
CROSS APPLY sys.dm_exec_query_plan ( A.plan_handle ) C
WHERE B.text LIKE ‘%Alter%’