SQL Scripts Manager, Simple-Talk’s Christmas present to its readers, is a free tool by Redgate. It now has an ‘add-in’ called Script Generator.
SQL Scripts Manager is a utility that allows you to run TSQL, PowerShell or Python scripts in a simple wrapper that provides the menu of available scripts, the User Interface for supplying parameters, and a separate GUI for displaying the output. I give a general introduction to it here in ‘SQL Scripts Manager: An Appreciation‘ and go on to show how it is equally effective for PowerShell scripting in ‘SQL Scripts Manager with PowerShell‘. It would be boring for you to repeat anything I covered in those two articles so if you’re unfamiliar with the tool, then please dip in to them.
After it was first released, it became obvious from feedback from the users that SQL Scripts Manager wasn’t yet ideal for end-users who wished to create their own scripts. Whereas an XML file seemed a trivial barrier to the developers and testers of the product, it proved to be tedious for the end-user who wished to use the tool to manage a large number of scripts. The problem was that it was originally conceived as a way of packaging a whole series of industrial-strength ‘issued’ scripts such as Ola Hallengren‘s amazing Database Maintenance package, reviewed here, whereas, experience has shown that it is currently also popular for deploying a collection of customised scripts for particular requirements.
The tool owes its existence to Red-Gate’s occasional ‘down-tools’ weeks, where the development teams take a quick break from the tasks they are engaged in, and organize themselves into different teams to work on whatever projects they want. Quite a lot can be achieved in these weeks and SQL Search, SQL Scripts Manager and MySQL Compare all came about through this work. It is great for FORGs (Ed: Friends Of RedGate) because this is the time we can give some input into what the Red-Gaters do without nervous interventions from the product managers. It isn’t exactly a free-for-all, since the participants have to present on what they are planning to do, and have to afterwards show what they’ve achieved.
The team who designed SQL Scripts Manager haven’t finished thinking of ways to improve the tool (if you want to get involved in Beta Testing the next version, please contact the team on their forum). The most pressing problem was designing a quicker and easier way of creating and submitting a script. This is where the Script Generator, which is an add-in, comes into play. At the moment, it is designed for SQL Scripts rather than PowerShell or Python, but if it proves popular, Hey, there will be more down-tools weeks and the team can be tempted to dust out the source from SubVersion.
Running SQL Scripts Generator with a SQL Script.
The sequence of events is this: Firstly, Copy the Script Generator to the install directory of SQL Scripts Manager (there is no installer as yet). Test your script first in SSMS. If it runs in SSMS, it is quite likely to run in SQL Scripts Manager. (You have to be more careful with implicit casting for displaying strings, I found).
Then fire up Script Generator from the SQL Scripts Manager directory.
Paste your script into the text editor panel, or load it via the tool bar or menu and fill the fields in the left hand pane.
Click ‘Save’ to build your .rgtool file. This file is compatible with SQL Scripts Manager, and it will appear in the list along with the scripts that are supplied. The reason the title is showing in red in the screenshot below isn’t because it is still glowing hot from the furnace, but is simply that the script hasn’t been signed by Red Gate. This doesn’t stop the script from running in any way at all. It just means it isn’t part of the distribution.
You can tweak the parameters in the XML file that is produced. Click on the OPEN button to execute the script and this dialog box pops up
Phew. It has taken on board the way I changed the ‘script target’ from ‘database’ to ‘server’, so that, the first time I run it I will need to supply it. (you can get it to remember all your servers, but only on a script-by-script basis). Now hit the ‘Run’ button.
Once you’re happy with the wrapped script, share it with the SQL Server Central community at http://www.sqlservercentral.com/Scripts/rgtool/ if you think others can benefit from it.
Scripts take hardly any time at all to generate. It is so much quicker than the original way of creating scripts from a template. Suddenly, I have no excuses for delaying a grand tidy-up of the heap of scripts lying around for occasional use. It is as easy as filling in a brief form. When you hit a problem (I stumbled over a minor problem with the output window of SQL Scripts Manager) it is lightening fast to modify a script in this add-in. This utility isn’t currently supporting parameterised scripts; but you can edit the XML .rgtool file created by the script, to include parameters. This tool is intended more for the SQL scripts that are executed within a server or database, and SQL Script Manager does all that for you by default.
Under the Kimono
The tool has produced a simple XML file as follows. Without too much difficulty, you’ll be able to see how you could alter it now it has been made in order to improve it. (I tweaked the size of the output window before taking the screenshot above!) The meaning of the XML that we’ve just generated can be looked up in detail here in The SQL Scripts Manager XML schema . You’ll see that only the ‘server’ and ‘database’ controls have been catered for in the Generator, but that is probably all that is needed for the average SQL Script and it is easy to add more in a text editor once the initial file has been generated. They are all listed here.
<?xml version="1.0" encoding="utf-8"?>
<description name="Basic Server Properties" icon="server_network"
<![CDATA[<p>This script lists out the most useful server properties for the server you specify</p>
<p><i>This routine merely gets the results of the most useful of the SERVERPROPERTIES parameters</i></p>]]>
<author name="William Brewer"
<output displaytype="Grid | OutputLog" width="500" height="400" />
<control type="header" label="Select server:">
<font name="Arial" size="12" />
<control type="server" />
('Server Name', (SELECT COALESCE(SERVERPROPERTY('ServerName'),'Unknown')), 'Both the Windows server and instance information associated with a specified instance of SQL Server.'),
('ComputerNamePhysicalNetBIOS', (SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS')),'NetBIOS name of the local computer on which the instance of SQL Server is currently running.'),
('InstanceName', (SELECT COALESCE(SERVERPROPERTY('InstanceName'),'default')),'Name of the instance to which the user is connected.'),
('Product Version', SERVERPROPERTY('ProductVersion'),'Version of the instance of SQL Server, in the form of ''major.minor.build''.'),
('Product Level', (SELECT SERVERPROPERTY('ProductLevel')),'Level of the version of the instance of SQL Server.'),
('CLR Build Version ',(SELECT SERVERPROPERTY('BuildClrVersion')),'Version of the Microsoft .NET Framework common language runtime (CLR) that was used while building the instance of SQL Server.'),
('CEdition', (SELECT COALESCE(SERVERPROPERTY('Edition'),'Error')),'Installed product edition of the instance of SQL Server.'),
('Edition ID', (SELECT CONVERT(NVARCHAR(127),SERVERPROPERTY('EditionID'))),'an identification number that represents the installed product edition of the instance of SQL Server.'),
('Engine Edition', (SELECT CONVERT(NVARCHAR(127),SERVERPROPERTY('EngineEdition'))), 'Database Engine edition of the instance of SQL Server installed on the server.'),
('Collation ', (Select COALESCE(SERVERPROPERTY('Collation'),'Error')),'Name of the default collation for the server.'),
('Collation ID', (SELECT CONVERT(NVARCHAR(127),SERVERPROPERTY('CollationID'))),'ID of the SQL Server collation.'),
('Comparison Style', (SELECT CONVERT(NVARCHAR(127),SERVERPROPERTY('ComparisonStyle'))),'Windows comparison style of the collation.'),
('SQL Character Set ID', (SELECT CONVERT(NVARCHAR(127),SERVERPROPERTY('SqlCharSet'))), 'The SQL character set ID from the collation ID.'),
('SQL Character Set Name',(SELECT SERVERPROPERTY('SqlCharSetName')),'The SQL character set name from the collation.'),
('SQL Sort Order' ,(SELECT CONVERT(NVARCHAR(127),SERVERPROPERTY('SqlSortOrder'))),'The SQL sort order ID from the collation'),
('SQL Sort-Order name', (SELECT SERVERPROPERTY('SqlSortOrderName')),'The SQL sort order name from the collation.'),
('Is Clustered?', (SELECT CASE WHEN SERVERPROPERTY('IsClustered')=1 THEN 'Yes' ELSE 'No' END),'Server instance is configured in a failover cluster.'),
('Is Full Text Installed?', (SELECT CASE WHEN SERVERPROPERTY('IsFullTextInstalled')=1 THEN 'Yes' ELSE 'No' END),'The full-text component is installed with the current instance of SQL Server.'),
('Is Integrated Security Only?', (SELECT CASE WHEN SERVERPROPERTY('IsIntegratedSecurityOnly')=1 THEN 'Yes' ELSE 'No' END),'Server is in integrated security mode.')
('Is Single User?', (SELECT CASE WHEN SERVERPROPERTY('IsSingleUser')=1 THEN 'Yes' ELSE 'No' END),'Server is in single-user mode.'),
('Edition ID', (SELECT CONVERT(NVARCHAR(127),SERVERPROPERTY('LCID'))),'Windows locale identifier (LCID) of the collation.'),
('License-Type', (SELECT SERVERPROPERTY('LicenseType')),'Mode of this instance of SQL Server'),
('Machine Name', (SELECT COALESCE(SERVERPROPERTY('MachineName'),'Error')),'Windows computer name on which the server instance is running.'),
('Num Licenses', (SELECT COALESCE(SERVERPROPERTY('NumLicenses'),'N/A')),'Number of client licenses registered for this instance of SQL Server if in Per Seat mode.'),
('Resource Last Update DateTime', (SELECT convert(char(11), SERVERPROPERTY('ResourceLastUpdateDateTime'),113)), 'Returns the date and time that the Resource database was last updated.'),
('ResourceVersion', (SELECT SERVERPROPERTY('ResourceVersion')), 'Returns the version Resource database.'),
('Filestream Sharename', (Select SERVERPROPERTY('FilestreamShareName')),'The name of the share used by FILESTREAM.'),
('Filestream Configured Level', (SELECT CONVERT(NVARCHAR(127),SERVERPROPERTY('FilestreamConfiguredLevel'))),'The configured level of FILESTREAM access.'),
('Filestream Effective Level',(SELECT CONVERT(NVARCHAR(127),SERVERPROPERTY('FilestreamEffectiveLevel'))), 'The effective level of FILESTREAM access.')
For me, it isn’t hard to be keen on SQL Scripts Manager because it is a quick way to put a wrapper on a script so as to allow anyone to use it. This ‘anyone’ includes myself in the future, who, I can predict from past experience, is slightly forgetful. With all my routine scripts all tidy, documented and consistent, I actually save myself the brief pause where I try to remember the complexities of how to use my own scripts. At the time I write this, there is a version of SQL Scripts Manager being tested (we need more volunteer beta testers- apply in the forum) that allows the user to change the scripts folder location so that a networked team can then share scripts. With that in place, SQL Scripts Manager becomes a lot more generally useful in the typical DBA ‘shop’.