Problem

A new feature in SQL Server 2012 is code snippets. In this tip I talk about what it is, how to use it and how to customize/add code snippets as needed.

Solution

IntelliSense was introduced in SQL Server 2008 and in SQL Server 2012 it has been enhanced to allow developers to insert code snippets while writing T-SQL code.

Inserting Code Snippets

To insert code snippets, right click on the query window or press CTRL+K followed by CTRL+X and click on the "Insert Snippet..." menu item as shown below:

When you click on the "Insert Snippet..." menu item, you will see a snippet picker with several options like shown below. For example, I want a script template for table creation and hence I click on Table and it will show all the templates available for tables:

As I want to create a table, I choose the Create Table option from the snippet picker and click on it:

It will bring up the table creation script template in the query window as you can see below, you can now modify it as per your need and execute it to create the table.

A category might also have multiple script templates (code snippets) as you can see below for Stored Procedure. Here we have three different templates to create a stored procedure, one is basic, the second is with a CURSOR as an output parameter and third is with an OUTPUT parameter.

I chose the basic template and this is the snippet that is created for a new stored procedure.

BEGIN, WHILE and IF Blocks

Not only can insert script templates, but you can also surround your code with either BEGIN, WHILE or IF blocks. To surround lines of code, select one or more lines of code and right click or hit CTRL+K followed by CTRL+S and then click on the "Surround With..." menu item as shown below.

In my case I chose to surround my lines of code with an IF block, you can see these lines of code are now surrounded with an IF block condition and the appropriate BEGIN and END commands.

Snippet Management

You can manage all code snippets using the Code Snippets Manager; to launch it, go to Tools in SSMS then click on "Code Snippets Manager..." as shown below or hit CTRL+K followed by CTRL+B.

With the Code Snippets Manager you can view all available templates (code snippets), add, remove and import. That is to say you are not bound to use only the existing templates, you can modify and add templates to suit your requirements:

All the templates are stored on the file system as XML files in the folder : \Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\SQL\Snippets\1033. Each group iscategorized and stored in different folders. For example the Table folder is shown below.

Customization

Now let's do some customization. What I am going to do is create a code snippet for table creation, but by default I want this code snippet to contain two additional columns CreatedBy (VARCHAR(100) NULL) and CreatedDate (DATETIME NULL) as this is the standard we follow when we create tables.

I will make a copy from an existing create table code snippet and make the required changes (you can even create an XML file from scratch if you want).

In the literals section of the file, I will add two literal nodes, one for CreatedBy (with ID = column3) column and another one for CreatedDate (with ID = column4) column as shown below:

Next I need to add data types for these columns and hence I am creating two more literal nodes, one for VARCHAR(100) NULL (with ID = datatype3) and another one for DATETIME NULL (with ID = datatype4) as shown below:

Now I need to change the code snippet and use the literals which I created above as you can see below. I have added column3 and column4 with datatype3 and datatype4 in the script.

Once you are done with changes in your code snippet XML file, you need to place it in the appropriate location or import it into the Code Snippets Manager as mentioned above. Then you will be able to use this new snippet as shown below:

If I chose this new snippet you will notice the two new columns are added:

Notes

The sample code, example and UI is based on SQL Server 2012 CTP 1, it might change in the RTM release.

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter
I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.