How to get a remote stored procedure, function, table in SQL Server 2008

Question

I checked the link below as part (@Oliver) posted as a duplicate of my Question. But this query returns the last execution of the script. This is not related to my question.

Recent Queries Completed for a Specific Database

I have a database with Sample_Training, and I created a stored procedure in it, and then I deleted it and now I want to get this remote stored procedure.

I am an employee in the company, so I do not have administrative rights

DECLARE @Date_From DATETIME = '2015-01-02' DECLARE @Date_To DATETIME = '2015-01-05' 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' 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 

In the above query, I can get the stored procedure, and now my question is how to get the function, the table.

Answer 1

I got a solution to my question. First you need to create a procedure

 CREATE PROCEDURE [dbo].[sp_Recover_Dropped_Objects] @Database_Name NVARCHAR(MAX), @Date_From DATETIME, @Date_To DATETIME AS DECLARE @Compatibility_Level INT SELECT @Compatibility_Level=dtb.compatibility_level FROM master.sys.databases AS dtb WHERE dtb.name=@Database _Name IF ISNULL(@Compatibility_Level,0)<=80 BEGIN RAISERROR('The compatibility level should be equal to or greater SQL SERVER 2005 (90)',16,1) RETURN END Select [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 

Follow the procedure below

 EXEC sp_Recover_Dropped_Objects 'Sample_Training','2015/12/24','2015/01/07'