Export SQL Stored Procedure to a File and Generate its Script

May 7th, 2020

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
Selecting the Generate Scripts command
Fig. 1 Selecting the Generate Scripts command
  • Select the objects to script
Selecting the objects you wish to script
Fig. 2 Selecting the objects you wish to script
  • In the Set Scripting Options window, select Script to File
Selecting Script To File
Fig 3. Selecting 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:

The stored procedure drop-down menu
Fig 4. The stored procedure drop-down menu

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

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:

Adding the stored procedure’s description
Fig.5 Adding the stored procedure’s description

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”:

Selecting the “Script Object as CREATE/ALTER” in the context menu
Fig.6 Selecting the “Script Object as CREATE/ALTER” in the context menu

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:

Selecting the “Convert EXEC to Script” command in the SQL Complete main menu
Fig.7 Selecting the “Convert EXEC to Script” command in the SQL Complete main 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:

The result of creating a script from a stored procedure
Fig.8 The result of creating a script from a stored procedure

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 review another important functionality – SQL Complete: Execution History that displays the previous queries. To do this, click on the SQL Complete: Execution History button:

Choosing the “Execution History” command in the SQL Complete main menu
Fig.9 Choosing the “Execution History” command in the SQL Complete main menu

You will see the following window:

The display of the executed scripts
Fig.10 The display of the executed scripts

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:

Setting up column filtering
Fig.11 Setting up column filtering

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

Stored procedure search in the “Execution History” tab
Fig.12 Stored procedure search in the “Execution History” tab

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:

Copying the selected script from the “Execution History” tab
Fig.13 Copying the selected script from the “Execution History” tab

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:

The “Execution History” command settings in SQL Complete
Fig.14. The “Execution History” command settings in SQL Complete

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.

Evgeniy Gribkov

Latest posts by Evgeniy Gribkov (see all)

Leave a Comment