Create Custom Templates in SQL Server 2012

Introduction

SQL Server 2012(Denali) has brought a lot of new features .One among them is the Code Snippets Feature.This feature will help us to create custom templates into the query window .In this article, we will look into as how we can get the benefit from this feature in a step by step way.

Article Aim

This article will aim at generating a custom FileTable template by using the Code Snippets feature of SQL Server 2012

How to invoke/use the already existing Code Snippets of SQL Server 2012

The code snippet can be invoked either by Edit->IntelliSense ->Insert Snippet or by the keyboard short cut Ctrl k, Ctrl X.

If we need to insert a Schema, just double click on the Schema snippet as under

and the snippet code will be generated

Creating Custom Snippet for FileTable

We can however, create our own custom snippet apart from the built in code snippets.Here we will create a custom snippet to create a custom template for FileTable

We will take a step by step approach for accomplishing this

Step 1:Create the snippet file and save it

As an initial step, we need to create a .snippet file which is basically a XML file that has Pre-defined schema. So our snippet file looks as under

<?xml version="1.0" encoding="utf-8"?>
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
  <CodeSnippet Format="1.0.0">
    <Header>
      <SnippetTypes>
        <SnippetType>Expansion</SnippetType>
      </SnippetTypes>
      <Title>Custom Templates for FileTable</Title>
      <Author>Niladri Biswas(9/29/2015)</Author>
      <Description>Code snippet Template for FileTable Creation</Description>
      <HelpUrl>http://msdn.microsoft.com/en-us/library/ff878091(v=sql.110).aspx</HelpUrl>
      <Shortcut></Shortcut>
    </Header>
	
    <Snippet>
      <Code Language="sql">
                       <![CDATA[
    		--Author:Niladri Biswas
		USE <database, sysname, AdventureWorks>
		GO		 
		IF	OBJECT_ID('<schema_name, sysname, dbo>.<table_name, sysname, sample_filetable>', 'U') IS NOT NULL
			DROP TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_filetable>
		GO
		CREATE TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_filetable> AS FILETABLE
		WITH
		(
			FILETABLE_DIRECTORY = '<file_table_directory_name, sysname, sample_filetable>',
			FILETABLE_COLLATE_FILENAME = <file_table_filename_collation, sysname, database_default>
		)
		GO
		]]>
    </Code>
    </Snippet>
	
  </CodeSnippet>
</CodeSnippets>

Save it into some folder and name it as CustomFileTableTemplate.snippet(Note that the file extension is .snippet) in a folder say CustomFileTableSnippet. In our case the entire path is C:\Users\niladri.biswas\Desktop\dotnetfunda\8.Creating Templates using CodeSnippet\CustomFileTableSnippet\CustomFileTableTemplate.snippet

Step 2: Register the custom code snippet in Code Snippet Manager

From the SSMS, Tools->Code Snippets Manager or invoke the short cut key as Ctrl K, Ctrl B. The code snippet manager window will open.

Click on the Add... button and choose the folder (here CustomFileTableSnippet) where we have generated the CustomFileTableTemplate.snippet file.

Step 3: Invoking the code snippet

We can invoke the code snippet either by Edit->IntelliSense ->Insert Snippet or by the keyboard short cut Ctrl k, Ctrl X.

Double click on the CustomFileTableTemplate and the code snippet will be generated as under in the Query Editor

N.B.~In the Code Snippet Manager, there is an Import Button which will basically import the snippet file instead of the folder.

>N.B.~In the Code Snippet Manager, there is an Remove Button which will basically remove the snippet folder

References

Custom Code Snippet - SQL Server Denali

Conclusion

So in this article we learnt about the Creation of Custom Templates in SQL Server 2012.Hope this was useful.Thanks for reading.Zipped file is attached.