Shortcut: Creating T-SQL Templates in SQL Server Management Studio (SSMS)

A few weeks back, I mentioned how useful templates can be. I said that I’d discuss how to create them later. Well that’s today.

I thought I’d take dropping a database as an example. The supplied template doesn’t work for me, so let’s create a new one.

Note: SQL Server 2016 SP1 introduced DROP DATABASE IF EXISTS but I find that option quite useless. It fails if anyone is connected to the database. And to disconnect people beforehand, you need to first check if it exists, so the statement is pointless.

Let’s start by opening the Template Explorer (from the View menu, click Template Explorer as it’s not open by default).

Next we look for a DROP DATABASE option, and right-click it.

When we click Open, we see the supplied template:

Importantly, note the text here that’s shown between the less than and greater than operators. This is an example of a parameter to the template.  Parameters look like:

<ParameterName, DataType, DefaultValue>

To create a new template, we could right-click the Database node in Template Explorer, and click New, then Template:

But to modify an existing one, it’s easier to right-click the one we want to modify (ie: Drop Database), and click Copy. Then right-click where we want it (ie: the Database folder), and click Paste. That’ll give us  a copy of the template as a starting point.

Rename the new template to Drop Database - GL, right-click it, and click Edit.

I’ve changed the template as follows:

First up, I’ve used semicolons as statement terminators. We’ve been asked to use them since SQL Server 2005 so I have no idea why the existing templates (and much sample code from the SQL Server team) doesn’t include them.

I’ve removed the comment headings. Otherwise, I’d need to do that every time. I’ve quoted the [name] column as I like to see the column names colorized properly, and this name would otherwise be blue. I’ve also aliased the table.

Next, to try to get around dropping a database with people already connected, I’ve altered it to single user, with an option to kick off all but a single user.

From the File menu, I click Save, then close the template window.

Now let’s try it. From Template Explorer, I right-click Drop Database - GL, and click Open, and my new template appears. Then, on the toolbar, I click the option to let me enter the parameters:

I enter the parameter value:

After I click OK, my template is ready for use:

The parameter value has been replaced wherever it had been used.

As a side note, this is still not 100% bulletproof. Note that the command is executed in the master database, and so there is no guarantee that the single user will be you. You could use RESTRICTED_USER but again, that might have the same issue. What is really needed is this:

DROP DATABASE IF EXISTS GregLowTemp WITH ROLLBACK IMMEDIATE;

But sadly, that command doesn’t exist. There really should be a way to reliably drop a database by standard T-SQL. Many people need to write drop and recreate scripts.

Finally, I’ll now point out that this code would be better as a snippet than as a template. Templates work well when they provide an entire script. This code is likely to be used as part of another script. In a later post, I’ll show you how to make this a snippet instead.

2018-01-04