Normally I don’t write about a topic if resources are widely available on the web. Now knowing how useful code snippets are, I was a bit surprised at the lack of good instructions on the topic. In the past, I had access to Red Gate at work so I was blessed with the power of SQL Prompt snippets. Gone are the days. I eventually dived in and learnt to use the Code Snippets Manager that comes with SQL Server Management Studio (2012). It involved some messy XML but at the end, it wasn’t too hard at all.
In this post, I am going to walk through one of my snippets and try to explain what everything means to the best of my knowledge:
<?xml version="1.0" encoding="utf-8" ?> <CodeSnippets> <CodeSnippet Format="1.0.0"> <Header> <Title>sp_msforeachdb</Title> <Shortcut></Shortcut> <Description></Description> <Author>Louie Bao</Author> <SnippetTypes> <SnippetType>Expansion</SnippetType> </SnippetTypes> </Header> <Snippet> <Code Language="SQL"><![CDATA[ set nocount on set transaction isolation level read uncommitted if object_id('tempdb.dbo.#results') is not null drop table #results create table #results ( ) insert #results exec sp_msforeachdb 'use [?] if db_name() like '''' begin end' select * from #results ]]> </Code> </Snippet> </CodeSnippet> </CodeSnippets>
The first thing you’d notice with the xml above compared to one of the examples found on Books Online is that that I have removed quite a few things. This is what I found the easiest way of creating code snippets.
This is the name you will see in the list of snippets. Make sure it is named properly so you can find it later.
Initially I thought it was the keyboard shortcut to invoke the snippet like how it is done in Red Gate’s SQL Prompt snippets. Turned out it isn’t all that useful as you still need to go through the Ctrl + K + X sequence to get to it. That’s why I left it blank.
Optional. Common sense applies here.
Again optional, nothing special, just put your name here.
As far as I know, there are two snippet types: Expansion and SurroundsWith. SSMS uses this value to determine which context menu will be used to display the code snippet.
Expansion = Edit -> IntelliSense -> Insert Snippets… Ctrl + K, Ctrl + X
SurroundsWith = Edit -> IntelliSense -> Surround With… Ctrl + K, Ctrl + S
I choose to create all my snippets as the Expansion type. It is just so much easier to understand and work with as I don’t need to worry about things like the $selected$ and $end$ tokens. One of the key reasons I decided to go with Expansion is that I just can’t seem to find a list of all these predefined tokens in order to take advantage of the SurroundsWith type. Furthermore, using SurroundsWith means I need to learn about all the silly tags like
<Declarations> <Literal> <ID></ID> <ToolTip></ToolTip> <Default></Default> </Literal> </Declarations>
and it is just simply unneccessary.
Self explanatory. Since this is a Visual Studio technology I assume you can have other languages like C#. For my purpose, a snippet is a snippet, SQL will do just fine.
<![CDATA[ code snippet ]]>
You don’t have to be an XML expert to figure this out. The actual code snippet lives inside this tag.
As I have shown above, that’s all the XML you’ll need to know to create a code snippet file. Next I will show you how it all ties together.
1. Save the code snippet file as sp_msforeachdb.snippet in the %userprofile%\Documents\SQL Server Management Studio\Code Snippets\SQL\My Code Snippets\ folder which should already exist. Once done, the file is immediately visible in SSMS (Tools -> Code Snippets Manager…, look inside My Code Snippets) and any modifications done to the file thereafter will be reflected immediately in SSMS. How cool is that! By the way, notice how I haven’t done anything fancy to Add… or Import…, all I did to make the snippet available was by saving a file.
2. Invoke the code snippet with the Ctrl + K + X key sequence.
The screenshot above shows the code snippet I have saved earlier. Some of you may notice at a glance that there are something missing from the list, yeah, all the default Microsoft code snippets! That’s because for faster access, I have removed (hidden) all the default ones that came with SQL Server 2012 by renaming…\Tools\Binn\ManagementStudio\SQL\Snippets\1033 to 1033 (Hidden).
3. Complete the query by filling the gaps.
set nocount on set transaction isolation level read uncommitted if object_id('tempdb.dbo.#results') is not null drop table #results create table #results ( name sysname ) insert #results exec sp_msforeachdb 'use [?] if db_name() like ''louie'' begin select name from sys.objects where type = ''u'' end' select * from #results
For more info, please refer to the links below:
Investigating Code Snippet Technology