In this article, we will look into as how we can create Custom Templates in SQL Server 2012
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.
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"?>
<Title>Custom Templates for FileTable</Title>
<Description>Code snippet Template for FileTable Creation</Description>
USE <database, sysname, AdventureWorks>
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>
CREATE TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_filetable> AS FILETABLE
FILETABLE_DIRECTORY = '<file_table_directory_name, sysname, sample_filetable>',
FILETABLE_COLLATE_FILENAME = <file_table_filename_collation, sysname, database_default>
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
Custom Code Snippet - SQL Server Denali
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.