SQL Scripts Manager is a free application from Red Gate for managing and executing SQL scripts.  It is designed  in such a way that the best way of understanding what it does to try it out.  It comes with a library of scripts that should be useful to you right away.  You probably won’t realize that those scripts that are included are there to serve a dual purpose. they are useful in themselves, but also to demonstrate what can be done with the tool.

You’ll notice that SQL Scripts Manager features the Ola Hallengren scripts that were described at length in the Simple-Talk article by Brad McGehee called Automate and Improve Your Database Maintenance Using Ola Hallengren’s Free Script  and Phil Factor’s scripts were described in SQL Server CRUD-Generation from System Views. Louis Davidson and Tim Ford contributed three scripts from their brilliant Simple-Talk book Performance Tuning with SQL Server Dynamic Management Views.

So why should one need SQL Scripts Manager

Why bother with SQL Scripts Manager when you already have SSMS, PowerShell, and SQLCMD? This is the first question I’ll tackle, but first, a bit of background.

The developers at Red Gate work single-mindedly on projects that can take many months to complete. Although they make Software Tools that are easy and interesting for the users, anything that requires months of toil, and extreme attention to detail is inevitably going to be rather less exciting to develop in the later stages. To get the fine detail right is not a creative task. One way that the company relieves the worst of the pressure on the development teams is to allow them occasional ‘down-tools’ weeks where they are allowed to work on their own projects. SQL Scripts Manager is the result of a couple of ‘Down-tools’ weeks. The people who work on projects such as SQL Compare or SQL Backup occasionally close their eyes and dream of creating something else, and the Down-tools weeks make that dream a reality. Already, several useful tools have emerged from this activity, such as SQL Search and MySQL Compare

SQL Scripts Manager started out in various coffee-time discussions about various dissatisfactions with SQLCMD and PowerShell for  the routine execution of  scripts. SQLCMD can do clever things, and is impressively fast, but if you need complex input forms or anything beyond the simplest of parameters, then you are suddenly feeling the design-restrictions of what is essentially a command line tool without a reasonable macro language. You can, of course, do a great deal with PowerShell, but this is never going to provide you with the means to find an execute scripts in an easily-maintained library of scripts. It is also idiosyncratic in it’s language. Once you’ve mastered it, you wonder what all the fuss is about, but  the creation of PowerShell scripts is is not for the occasional user.

When a number of ideas coalesced into thinking about a tool, a number of  design criteria coalesced. These were that …

• The  tool ought to  provide a versatile form-generation, so that a user could execute a script without special training. SSMS provides a simple form for filling in a template, that could then subsequently be executed. Too simple, far too simple. You cannot even generate a list or a combo to choose alternatives. With SSMS, it was a germ of a good die that never got finished. As well as providing a form that allowed the user to select the database and server, with may me a text field, and the ability to select one of a list of parameters,
• There had to be a better way of processing the results of a SQL query. You can, with SSMS, save the file as CSV or paste it into Excel, but what about the myriad of other things that one might want to do with data. Here PowerShell shines, but only for the departmental geek. For SQL Scripts Manager, The answer was to allow the loading of dynamic Python Libraries to process the results. There is an example provided that was used to save templates in the local SSMS template directory from Phil Factor’s scripts.
• It has to be dead easy to use. Any DBA will have scripts that need to be executed by whoever is on duty when unpleasant events happen. The obvious example is disaster recovery, but there are a number of eventualities that can happen to a production system that the typical DBA will have scripts for. These have to be executed by production staff for whom PowerShell may be a closed book, and SSMS a mystery. For this, SQL Scripts manager is ideal; but even when one has the special keystrokes of SSMS imprinted on one’s medullary cortex, it is great to be able to locate the right script, confirm that it is correct, point it at the correct recalcitrant database, and fire it.
• It must be possible to certify scripts so that the user is aware if any unauthorised, or unofficial, alteration has been made to any of them.
• The scripts, with all their extra parameters and user interface,  must be stored in XML and editable in any XML Editor, and simple scripts should be easily created from example XML documents.

SQL Scripts Manager started in the first of Red-Gate’s Down-Tools weeks; the team continued the work in the second Down Tools week, by the end of which it was ‘nearly’ ready for release. Then, over the next few months, the product was refined and polished, and more scripts were added from more authors from the Friends of Red Gate (FORG).  There are now  28 scripts from 9 contributing authors.

Running a script

Before you run a script, it is worth checking the source and the description. Some scripts require you to see the window that gives the opportunity to view the description….

…or the source.

When you then click to continue, you will then will see the input form, which will vary according to the script. Here is a pretty simple one. The ‘Save preset’ button allows you to save a commonly used server for this script as a default.

When you click ‘Run’, you will, with luck be met by a result displayed in grid form like this one seen below (Tracy’s script).

Creating a script file

When creating a new script, it is probably best to to start with a copy of one of the .rgtool XML files provided with SQL Scripts Manager. (select File > Show Scripts Folder from the main SQL Scripts Manager window to find where they are stored). There are also two simple template scripts that can be downloaded at the bottom of the article. If you want to do it from scratch, You’d  make a copy  of your TSQL Script and convert it into an .rgtool XML script file by wrapping it with appropriate XML elements and attributes, such that the resulting file is a valid SQL Scripts Manager XML file. You should also rename your script file to have an .rgtool extension. This XML file  has a schema that is documented in The SQL Scripts Manager XML schema.  When experimenting,  you can alter the GUI definition which is part of the XML script file, by  adding controls for selecting a SQL Server instance or database, and to map other controls to parameters required by your script (such as text boxes and option buttons). (See Types of control for detailed information)

Once you’ve finished editing your new .rgtool file, save it to the SQL Scripts Manager scripts folder, located at \Documents and Settings\All Users\Application Data\Red Gate\SQL Scripts Manager\ by default. and restart SQL Scripts Manager

If your .rgtool file is valid, the script will be shown in the list on the main SQL Scripts Manager window, ready to run. If there are problems with the file, it is associated with the ‘<Load Error>’ tag, and the error is shown beneath the script title in the scripts list:

Of course,  SQL Scripts Manager will only report load errors that relate to problems with the XML structure of your .rgtool file, whereas any syntax or logic errors in the script code (the code contained within the <script> tags in your .rgtool file), will not be evident until you run the script using SQL Scripts Manager.

Here is just about the simplest template file you could use, with an explanation of what goes where. It is used to run a script against whatever database you select. It can be downloaded at the bottom of the article. I’d guess that this will cover most of your requirements if you have a number of relatively simple scripts.

There are a large number of icons compiled into the program  that you can use just by specifying them in the XML file without having to supply your own. These are in the downloads at the bottom of the article. You can provide your own icon, but it  has to be copied to the same folder as the script.  The icon must be  referenced by its full file name, (e.g. foo.ico), in the script 

You will notice the <signature></signature> tags at the end of the XML document. Once you have created the scripts and had them signed off, they can be given a digital certificate by Red Gate. This will inform whoever runs the script that it has not been tampered with. There are plans afoot to provide a tool to do this, but exact details are yet to be announced.

The user interface

Here is one of the more complex forms that are presented in one of the sample scripts.

This is entirely determined from the following xml fragment from the corresponding XML file. It isn’t much harder than XHTML to figure out.. All the controls, and many more besides are described here.

Community-supplied Scripts

Currently, the plan is for the SQL Server Central script repository to be used for community-contributed scripts.  There will be support the addition of scripts wrapped up to run inside SQL Scripts Manager.

More information

• How to add your own scripts
• The SQL Scripts Manager XML schema
• SQL Scripts Manager: Types of Control
• SQL Scripts Manager Forum
• SQL Scripts Manager troubleshooting and support