Home » SQL Server » How to Create Custom Snippets in SQL Server 2012?

How to Create Custom Snippets in SQL Server 2012?


3 steps to create custom Snippets in SQL Server 2012 :-

  • Create a snippet using XML
  • Register the snippet in SQL Server Management Studio (SSMS)
  • Invoke the snippet when using Query Editor

Step 1. Create a T-SQL Snippet File with XML

A snippet that can use to write a backup statement for any database. Save XML file with .SNIPPET extension.

<?xml version="1.0" encoding="utf-8" ?>

<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">

<CodeSnippet Format="1.0.0">

<Header>

<Title>FullBackup</Title>

<Description> Full DB Backup </Description>

<Author> Rohit Garg </Author>

<SnippetTypes>

<SnippetType>Expansion</SnippetType>

</SnippetTypes>

</Header>

<Snippet>

<Declarations>

<Literal>

<ID>FulDBBackup</ID>

<ToolTip>Take full DB Backup</ToolTip>

<Default>FulDBBackup</Default>

</Literal>

</Declarations>

<Code Language="SQL">

<![CDATA[

BACKUP DATABASE <DATABASE NAME> TO Disk = <BACKUP FILE LOCATION>

]]>

</Code>

</Snippet>

</CodeSnippet>

</CodeSnippets>

Step 2. Register the Snippet with SSMS

After creating XML file, use the Code Snippets Manager to register the snippet with SSMS. You can either add a new folder based on the snippet category or import individual snippets to the My Code Snippets folder.

By default, all T-SQL snippets are stored in the following folder and saved as .snippet files:

C:Program FilesMicrosoft SQL Server110ToolsBinnManagementStudioSQLSnippets1033

To add a snippet folder:

  • Launch SSMS
  • Select Tools from the menu items and click Code Snippets Manager, which launches the Snippet Manager
  • Click the "Add" button
  • Browse to the folder containing FullBackup.Snippet file, and click the Select Folder button

The next step is to import the snippet in to SSMS:

  • Launch SSMS
  • Select Tools from the menu items and click Code Snippets Manager
  • Click the Import button at the bottom
  • Browse to the folder containing FullBackup.snippet file and select FullBackup.snippet file, then click the Open button

Step 3. Invoke or Insert a T-SQL Snippet from Query Editor

You now have a snippet called FullBackup that you can invoke from the query editor with the shortcut key by pressing CTRL + K + X. Then select the category folder in which you’ve stored the snippet. You could also right-click on the context menu in query editor and select Insert Snippet.

You can also invoke a snippet by right-clicking on the context menu in the query editor. This will present you with various Snippet Options.

Using these steps, you can create T-SQL code snippets and register them with SSMS. You can also create complex snippets of various regular tasks and make your life managing SQL Server much easier.

Although T-SQL snippet is basically for developers but I feel it is very use for DBAs as well. You can add you development & DBA snippet for daily use like this.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s