SQL Server 2012 introduced a new feature – code snippets inside Management Studio.
And even better, you can create your own snippets.
What is a snippet?
Snippets come from the programming world and enable reducing the coding time of repeating code sections (loops, conditions and so on). This feature has been a part of Visual Studio for the last few versions, and now it comes to Management Studio with an arsenal that includes encapsulation of code sections with a condition, a loop or a begin-end block, and patterns for creating tables, procedures, indexes and so on.
But in my opinion, the real power here is the option to create new snippets on ourselves.
For your use and reference, I have created ten downloadable snippets for some of the common commands and scripts I use daily:
Count rows per partition – Shows the number of rows for each partition of the highlighted table.
Count rows – Shows the number of rows in the highlighted table.
DBCC InputBuffer – Shows the last statement sent from the client of the highlighted session number.
Index fragmentation – Shows index fragmentation for the highlighted table.
Index usage – Shows index usage stats for the highlighted table.
Objects containing string – Shows the programmatic objects that contain the highlighted string.
SP_HelpIndex – Shows the highlighted table’s indexes with their columns (included columns are not shown).
Show current locks – Shows lock status per session and object.
Show current running queries – Shows the running queries with their execution plans.
Create numbers table – Creates a numbers table and populates it with 10,000 rows.
To download the snippets, click here.
How to import the snippets to Management Studio:
Extract the downloaded file to a folder on your computer
On Management Studio 2012, go to Tools -> Code Snippets Manager
Choose “Import”
Navigate to the folder the downloaded file was extracted to
Select all the files in the folder, and click “Open”
Choose the location for the snippets and click “Finish”
How to use the code snippets:
From my checks, there are two snippet types: “Surround With”, and “Expansion”.
The first type is used to surround a highlighted code section. An example is surrounding a code section with a while block.
The second type is used as a pattern for creation of common objects as indexes and tables. The snippet will insert placeholders for the user to fill. switching between the placeholders is done using the Tab button. This type of snippet can also be used as a wrapper for a hard-coded text like some of the snippets I created.
The first seven snippets I created are “Surrounds With”, and the last three are “Expansion”.
To use “Surrounds With” snippets:
Highlight a code section, right click and select “Surround With” (or use Ctrl+K+S)
Choose the location you selected for the snippets (or choose “Function” to use snippets supplied with Management Studio)
Choose the relevant snippet from the list
To use “Expansion” snippets:
Right click and select “Insert Snippet” (or use Ctrl+K+X)
Choose the location you selected for the snippets (or another location to use snippets supplied with Management Studio)
Choose the relevant snippet from the list
How to create new code snippets:
The snippet files are just xml files and are quite easy to comprehend.
“Surrounds With” snippet example:
“Expansion” snippet example:
The easiest way when creating a new snippet is taking a look at an existing snippet file and changing it to fit your needs. A good reference for a “Surrounds With” snippet is the “Count rows” snippet, and good references for an “Expansion” snippet are the “Create numbers table” and “Show current locks” snippets.
The two important things to define are the SnippetType element (“SurroundsWith” or “Expansion”) and the Code element.
In the Code element, when creating a “Surrounds With” snippet, $selected$ is the text highlighted in Management Studio.
When creating both snippet types, you can use parameters. Those parameters should be defined under the Declaration element, and in order to treat them as parameters in the code, they should be written between dollar signs, as seen in the previous example.
How to delete the created/imported snippets:
The snippets are created under C:Users<YourUser>DocumentsSQL Server Management StudioCode SnippetsSQL. Under the location you chose, you will see the snippets, and deleting them will also delete them from Management Studio.