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.
