solving sql server problems for millions of dbas and developers since 2006

Top SQL Server Tools

SQL safe backup

SQL Monitor

SQL diagnostic manager

dbForge SQL Complete

SQL compliance manager

Getting started with Code Snippets feature of SQL Server Denali

By: Arshad Ali | Read Comments | Print

Arshad is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

Related Tips: 1 | 2 | 3 | 4 | 5 | More

Problem

A new feature in SQL Server Denali 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 Denali 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:

inserting code snippets while writing t-sql code

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:

sql query insert snippet menu item

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

create table from the snippet picker

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.

table creation script template

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.

a category might also have multiple script templates

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

the basic template

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.

you can surround lines of code with blocks

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.

use of the if block

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.

from the tools menu in ssms

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:

launch the code snippets manager in ssms

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 is categorized and stored in different folders. For example the Table folder is shown below.

all the templates are stored on the file system as xml files

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:

creating a code snippet for table creation

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:

add data types for these columns

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.

change the code snippet and use the literals

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:

import the changes in your code snippet xml file into the code snippets manager