Export SQL Stored Procedure to a File and Generate Its Script

In previous articles, we have reviewed a general algorithm of finding and deleting incomplete open transactions in SQL Server databases, created a table for fixing incomplete transactions and a CRUD-stored procedure, and implemented numerous settings that will make our document workflow productive and handy.

Let’s now export and test a stored procedure, generate its code, and turn it into a script – it will be executed on any host or a group of hosts by specifying proper entry parameters.

Export Stored Procedure in SQL Server

It can also be quite useful to generate a script for creating the necessary database objects, for example, generate a script to export a stored procedure to a file or copy the stored procedure to other solutions. To do this, follow the following steps:

• In the Object Explorer, right-click on your database
• Select Tasks from the context menu that appears
• Select the Generate Scripts command 

• Select the objects to script

• In the Set Scripting Options window, select Script to File 

• Run the generated script against the target database

When all these steps are performed, we will get a generated script of the exported stored procedure.

Testing a Stored Procedure

Let’s return to a stored procedure that we have created in part 2. If we drag the cursor to a stored procedure, a drop-down menu will pop up:

To work properly, your stored procedure should have a description. If it hasn’t been added yet, please run the following script:

xxxxxxxxxx

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identification of frozen transactions (forgotten ones that do not have active requests) with their subsequent removal' , @level0type=N'SCHEMA',@level0name=N'srv', @level1type=N'PROCEDURE',@level1name=N'AutoKillSessionTranBegin'

GO

Or go for the object’s extended properties:

You can also add a description using this method.

Stored Procedure’s Code Generation

You can call it with a right-click on a stored procedure’s drop-down menu, then select “Script Object as CREATE/ALTER”:

Once you do this, a script for the object change will pop up – in our case, it will be the stored procedure change. If there is no object, there would be a script for creating it. This script can be easily moved to proper MS SQL Server instances.

Turning Stored Procedure Code to a Script

You can select “Convert EXEC to Script” in a drop-down menu:

Instead of calling a stored procedure, a script will be created, entry parameters will become variables, and the stored procedure content will become a script. It’s a handy feature when testing your code:

Now you can run the derived script on any host or a group of hosts by setting entry parameters’ proper values.

Query Creation History

Let’s now see another important functionality – SQL Complete: Execution History that displays the previous queries. To do this, click on the SQL Complete: Execution History button:

You will see the following window:

The top left corner of the window contains filters for words and time range, while the right corner shows the number of queries that were found based on the data entered in the filter. The bottom side of the window shows the query selected from the table.

The table consists of the following columns:

1. Status – shows whether a query was completed successfully (white checkmark in a green circle) or not (white cross in a red circle)
2. QueryText – shows query text
3. Size (Bytes) – shows size measured in bytes
4. Execution On – shows date and time when a query was executed
5. Duration – shows the time it took a query to be executed
6. File – shows the file’s name and a full path to it
7. Server – shows the server’s name
8. User – shows a user who executed a script
9. Database – shows a database in which a script was executed

The search for the “QueryText” and “Execution On” columns is done in the “Search” and “From/To” filters.

You can also sort data columns in ascending or descending order by clicking on a header of a proper column. By default, the “Execution On” column sorting is enabled in descending order.

You can sort multiple columns at the same time by holding a SHIFT button. You can also set up more complex filters by clicking on the filter sign:

Let’s now enter our stored procedure name “AutoKillSessionTranBegin” in the “Search” filter:

As a result, we get a full creation history of the srv.AutoKillSessionTranBegin procedure that was described before.

If you right-click on a proper table row, you can open a script in a new window or copy it to the clipboard for pasting it wherever needed:

You can open a script in a new window by double-clicking on the desired script in a table row.

You can also specify for how long the execution history is stored, max query size, and the history storage path:

You can read more details about the Execution History functionality here.

That’s all, folks. The new functionality has been developed, tested, and moved to proper MS SQL Server instances.