SQL Server Templates

SQL Server Management Studio comes with a bunch of pre-built templates for scripting out procedures, functions, tables, administrative functions and more. All they are essentially are a .sql file with some special syntax in the script where values can be pasted into place. The special syntax is pretty easy to get a handle on, but at first glance can seem a little strange. Here's a sample of what you might see if you were looking at a SQL Server template (minus all the red lines I added).



There are several parts to this. Let's start with the window that's currently being displayed (in the picture, it's the window with the header "Specify Values for Template Parameters"). To get to this window, you must have a query window open in SSMS, and it wouldn't hurt to have a few tags in it (more on that later). In this query window, press ctrl+shift+M. I have no idea what the M is a mnemonic for... para(M)eters? Whatever. Once you're in this view, you'll be able to replace the special notation inside the script with values you input on this screen.

So how do you set that up? You put in tags with the notation
<[Parameter], [Type], [Value]>

[Parameter] is like the identifier for your replacements. Anywhere in your script, any time you have, say tagged in the script, all instances of that Database tag, will be replaced with the value you put in that popup window. This is the only part of the tag which is NOT optional. Think of it as your primary key.

[Type] is really just a hint to yourself about what datatype the script is expecting. Often times it's irellevant. If you're building a new Proc, you might have a tag which says , but you could just have easily put "Int" instead of "Sysname" and it wouldn't care. It's not a data type validator, it's just so that if you have a script which requires data of a certain type you know what to enter. It's worth noting that this is entirely optional. Also, if you change this data type throughout the script, it won't care. Only the first instance of the tag will be used to populate the [Type] column in the popup window.

[Value] is an optional default value in the field. In the example I showed above, more often than not, I'm looking for information_schema.columns, so I set the default of Parameter:SubType to "Columns". If i wanted to make it Routines or Tables, I just have to replace the "Columns" value with the value I want.



When you then click OK, all the values you have are propagated into those replaced tags throughout your script. It's worth mentioning that SQL treats each replacement of a value as a single undoable action, so if you've got a huge parameter list and you accidentally hit OK too soon, all the values you did (or probably didn't) submit will have to be undone one by one till you get back to your original script.



My final note on this is that if you don't have SQL 2012, or do and cant figure out how to use the snippets feature, get something like the SSMS Toolspack. It's free, and comes with a bunch of helpful tools, my favorite of which is a really simple code snippet interface. I've built little 2 - 3 character shortcuts for some of my most commonly used functions, such as the one I listed here. I type "info"+tab and it populate my query window with that code. I've got others as well, "tab"+tab for tables, "IX" for indexes and so on. When you combine this template format with the ability to call these templates up in a fraction of a second, you can really cut down extra typing time a LOT.