SQL Server - Adding Code Snippet and Using Existing Code Snippet

Once I wrote a post on SQL Server Template Explorer that describes available TSQL templates and how they can be used. Just like templates, we have been given some ready-made Code Snippets that help us to construct the statements easily. Not only that, it allows us to add our own snippets using adding Code Snippet Manager.

First of all, let's see how we can use existing code snippets. Assume that you need to create a SQL Login and you cannot remember the syntax. What you can do is;

    1. Either select Insert snippet... context menu in the Query Window or press Ctrl+K and Ctrl+X  (Press Ctrl and hold, and then press K and X).

    

    2. Select Login folder and then select Create SQL Authentication Login.

    

    3. Change the code as you need.

    

Note that, like adding codes using Template Explorer, you do not get another interface for changing values. Values need to be manually changed.

If you need to add your own code snippet, you can take copy of an existing one, change as you want and save with your own name. Assume that you need to add Azure Firewall Setting as a code snippet. If so, here are the steps;

    1. Open the SQL Server Code Snippet folder. If you have selected the default location when installing SQL Server, the path would be C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\SQL\Snippets\1033.

    2. Create a folder called Azure (or name as you need).

    3. Update the SnippetsIndex.xml. Add the following node to the file. This node is for the newly created folder. (** Note that this file cannot be modified if you have not opened the editor as Administrator. If change this using Notepad, open the Notepad as an Administrator and then open the file for modifictions).

    4. Take a copy of existing snippet and place in Azure folder. I have taken Create SQL Authentication Login.snippet and renamed as Create Azure Server Level Firewall Rule.

    5. Open Create Azure Server Level Firewall Rule file (Open as an Administrator) and modify -->