How to retrieve dropped stored procedure, function, table in SQL Server 2008
I got Solution to my question. First Need to create a procedure
CREATE PROCEDURE [dbo].[sp_Recover_Dropped_Objects] @Database_Name NVARCHAR(MAX), @Date_From DATETIME, @Date_To DATETIMEASDECLARE @Compatibility_Level INTSELECT @Compatibility_Level=dtb.compatibility_levelFROM master.sys.databases AS dtb WHERE dtb.name=@Database_NameIF ISNULL(@Compatibility_Level,0)<=80BEGIN RAISERROR('The compatibility level should be equal to or greater SQL SERVER 2005 (90)',16,1) RETURNENDSelect [Database Name],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'AND [TRANSACTION ID] IN (SELECT DISTINCT [TRANSACTION ID] FROM sys.fn_dblog(NULL, NULL)WHERE Context IN ('LCX_NULL') AND Operation in ('LOP_BEGIN_XACT') And [Transaction Name]='DROPOBJ'And CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)And Substring([RowLog Contents 0],33,LEN([RowLog Contents 0]))<>0
Execute the procedure as below
EXEC sp_Recover_Dropped_Objects 'Sample_Training','2015/12/24','2015/01/07'