How do I find an old stored procedure in SQL Server?

How do I find an old stored procedure in SQL Server?

Not directly within SQL Server. Unless you have kept a script in source control or elsewhere your only option is to restore an older backup to another environment and script out the old version from that. No, unless the SSMS query window you used to alter the procedure still has the old text in its undo buffer.

How do I revert a stored procedure?

8 Answers

1. Shutdown the server (or at least put it into read-only mode)
2. Take a full back up of the server.
3. Get a copy of all the db log files going back to before when the accident happened.
4. Restore the back up onto another server.
5. Using db admin tools, roll back through the log files until you “undo” the accident.

How do I restore a stored procedure in SQL Server?

Restoring the full database backup

1. Restore the full database backup on a test server.
2. To create a DDL script for the object you need, right-click the stored procedure you want to restore in the SQL Server Management Studio’s Object Explorer.
3. Select Script Stored procedure as | Create To |New Query Editor Window.

How can check stored procedure change in SQL Server?

You can use sys.proceedures to find the date of the most recent modification for stored procedures;

1. SELECT [name], create_date, modify_date.
2. FROM sys.procedures.
3. ORDER BY 3 DESC;

Who deleted the database SQL Server?

Right click SQL Server Instance and Select Reports -> Standard Reports -> Schema Changes History as shown in the below snippet. 3. This will open up Scheme Changes History report which will have the details about who deleted the SQL Server Database along with the timestamp when the database was deleted.

What is Fn_dblog?

The fn_dblog() function (formerly known as the DBCC command) is one of several undocumented functions for SQL Server. It allows you to view the transaction log records in the active part of the transaction log file for the current database.

How can I see who deleted a table in SQL Server?

Is there an older version of SQL Server procedure?

there is no versions within sql server for stored procedure and hence you cannot get an older version of procedure from a db. you should use a source control for such purposes – ughai Oct 19 ’15 at 6:46.

How to view the stored procedure in SQL Server?

Right click on the stored procedure and select Script Stored Procedure as | CREATE To | New Query Editor Window / Clipboard / File. You can also do Modify when you right click on the stored procedure.

How to view the definition of procedure in query editor?

To view the definition of a procedure in Query Editor. System Stored Procedure: sp_helptext. In Object Explorer, connect to an instance of the Database Engine. On the toolbar, click New Query. In the query window, enter the following statement that uses the sp_helptext system stored procedure.

How to change the name of a stored procedure?

On the toolbar, click New Query. In the query window, enter the following statement that uses the sp_helptext system stored procedure. Change the database name and stored procedure name to reference the database and stored procedure that you want.