Archive for the ‘Query and Text Editors’ Category

In my earlier articles, I wrote about how to insert buit-in code snippet in SQL Server script. But sometimes due to the requirements, we need to create our own custom snippet. Once you create the custom snippet(s) , you need to register these snippets, because without registration you cannot use in the SQL Server Query editor.

There are two ways two register the code snippet. Given below are the details.

Method 1 :
In this method, you can add folder having code snippet file(s). The advantage of this method is that you do not need to do one by one code snippet files.
Let me explain it step by step.

Step 1 :
In this step, browse the Tools menu and select the Code Snippet Manager as shown in the picture below.


It will open the code snippet manager as shown in the picture below.


Step 2 :
As you can see above, multiple built-in folders and snippet files for standard objects code snippets are available in the Code Snippet Manager. Now, you need to add custom folder and snippet files in the Code snippet Manager. Click on the Add.. button as shown in the picture below.


Step 3 :
Once you click on the Add.. button, it will open the files explorer, now you need to select the folders having snippet files.You cannot select an individual file using Add.. button. Just to explain this registration, I created one folder namely Custom and placed few code snippet file in it. Once you select the folder click on Select Folder button as shown in the picture below.


Step 4 :
Now, you can see that custom folder and snippet files are registered and added in the Code Snippet Manager as shown in the picture below.


Method 2 :
In this method you can add multiple files into any custom folders.
Let me explain this method step by step.

Step 1 :
This step will be same as method 1.

Step 2 :
Once you are in the Code Snippet Manager, click on Import button as shown in the picture below.


Step 3 :
Once you click on Import button, it will open an explorer and allow you to select code snippet file(s).
Once you selected the file(s), click on Open button as shown in the picture below.


Step 3 :
Once you select the code snippet file(s), it will open an another import window and ask in which custom folder you want to add this file(s) as shown in the picture below.


Step 4 :
Once you select any custom folder for the code snippet file(s), click Finish button. The snippet file(s) will be available in that custom folder as shown in the image below.


Reference : MSDN

Read Full Post »

In my earlier article, I wrote about the new feature of SQL Server 2012 namely Insert Snippet. This is quite a handy feature which can save a lot of your time. This feature is shipped with a lot of standard objects snippet with the customization options.

But there were two big questions in front of me. 1) Why do we need to customize it? 2) How can this save our time ?
Answer :The reason for customization is that, each and every company follows some standards to create standard object (Table, view etc.) of SQL Server. For example the name of the table should start with tbl_ or there might be some additional fixed column with some default values in each table etc etc. Therefore, once you customize the snippet as per your requirements, then you can use it every time.

Prerequisite: How to insert the Snippet in SQL Server 2012.

Solution :
Let me explain, how to customize the standard object table snippet Step by Step :

Step 1 :

First of all, I will take you to the path where snippet of the standard objects resides in the SQL Server 2012.

To find the path you need to go to Tools and select Code Snippets Manager as shown in the picture below.


Once you open the Code Snippets Manager, select the appropriate object and you can see the path of that particular object as shown in the picture below.

Step 2 :
As you can see the path of table snippet in the above picture, the next step is to browse the table path and find the standard table snippet namely Create Table.snippet.


Step 3 :

Now, you are in the standard table snippet folder,  so you need to make a copy of the standard table object namely “Create Table.snippet” and rename it to “Create Sample Table.snippet”  as shown in the picture below.


Step 4 :

Open the newly created file Create Sample Table.snippet, first and foremost, change the title as highlighted in the given below picture.


Step 5 :

Once you change the title copy the selected area as shown in the picture below and paste twice after <Literal> because we need to add two additional columns of the table in the snippet.


Step 6 :

Lets rename the column name and data type of the columns ([Creation_Datetime] & [Created_By] having data type datetime and nvarchar(50) respectively as shown in the picture below.


Step 7 :

Once you create the additional column, do not close the file, just scroll down a little bit in the same file and copy the selected area ($Column2$ $datatpe2$) and paste it twice and then rename it to $column3$ & 4$ and $datatype 3$ & 4$ respectively as shown in the picture below.


Step 8 :

Once all the changes are done in Create Sample Table.snippet save and close the file and open SQL Server Management Studio.

Step 9 :

Lets insert the customized table snippet in Query window as shown below.




Read Full Post »

SQL Server 2012 came up with many new features and enhancements. Insert Snippet is one of the new features introduced in it. This feature reduces the development time and increases productivity. How ? It basically generates the code snippet for the most frequently used objects in SQL Server instantly (with few clicks or key strokes).  But it is not limited to the frequently used objects, custom code snippet template can also be added in it and can generate it at any point of time, to make life easier. How to create custom code snippet will be demonstrated in my up coming article.

Given below are the object’s code snippet available in SQL Server as a standard template :

  • Function 
  • Index
  • Login
  • Role
  • Schema
  • Stored Procedure
  • Synonym
  • Table
  • Trigger
  • User
  • User Defined Data Type
  • User Defined Table Type
  • User Defined Type
  • View

Let me insert a Create Table code snippet step by step using Insert snippet .

Step 1:

Open a new query window in the Database Engine and place the cursor where the code snippet has to be inserted.

Step 2:

In this step, launch snippet picker to insert Snippet. It can be done using three different methods. Given below are the details of these methods.

Method 1:

Press CTRL+K, CTRL+X to launch snippet picker.

 Method 2:

Go to the Edit Menu then point to IntelliSense and then select Insert Snippet to launch snippet picker as shown in the picture below .


Method 3:

Right click and select Insert Snippet to launch snippet picker as shown in the picture below.


Step 3:

Once the snippet picker is launched, select table from the list as shown in the picture below.


Step 4:

After selecting the Table press TAB or Enter key to proceed as shown in the picture below.


Step 5:

After selecting the Create Table script as show in the above picture then again press TAB or Enter key to generate the code snippet as shown below.


Step 6:

Notice that the create table code snippet in the above image has been generated. Now just change the highlighted area to create desired customized table.  Additional columns as well can be added.


Conclusion :

Insert Snippet is a very nice feature introduced in SQL Server 2012. Time saving tool. The example discussed above took less than 10 seconds to create it. Also it is not limited to create table code snippet alone, any object code snippet can be created in less than 10 seconds.

Will post how to create the customized code snippet in my upcoming post.

Read Full Post »