Writing TSQL faster with templates

SSMS Templates

The management tools that come with SQL Server are the way that most DBAs access and administer their database environment. SSMS has plenty of shortcuts and times saving features, one of which is the use T-SQL templates. SSMS templates are accessed via their own menu and are stored in folders just like you would access files on a windows explorer window. SSMS comes with a whole load of templates ready to run but you can add to and alter these very easily.

Firstly, lets look at getting a template onto the query editor and running it successfully.
Right, open up an SSMS session and hit Alt + N (or Ctrl + N for SSMS for SQL 2005) to open a new TSQL editor window. Now its possible that you will have the templates toolbar ready to use already but hit Ctrl + Alt + T to make it active. Lets pick something simple to start with, open up the Database folder and drag the Create Database template over to your query editor page. Right, we have the text on the page but we have to do a little work before we can hit F5 or click Execute.

You'll see that there are sections of code inside < > brackets. These are SSMS template parameters, we need to take this code out and replace it with something specific to our environment in order to make the script parse and then execute successfully. We could swipe the parameter with our mouse but that could get tedious if we pop out a template with loads of them, its easier to just press Alt + Q, Alt + S (Ctrl + Shift + M for SQL 2005 and earlier). This brings up the parameter completion dialog for you.

Simply type the database name in the higlighted area and press Enter. Job done, it is now ready to run, the text that you entered in the parameters dialog has been put in three places and the script will now successfully create a database (with all the default options such as file locations that are set at server level) providing you have security on the server to execute this command.