Blog Archives

Exploring SQL Server 2012: Code Snippets

One of the new features available in SQL Server 2012 is the Code Snippet Manager.  Previously this was available through third-party tools and add-ins.  The Code Snippets Manager is available through the Tools menu, along with some other nice built-in tools as well for this release.

Here you can see that there are many nice built-in code snippets included off the shelf.  The tool allows you to insert the XML formatted snippets (a la Visual Studio).   Click here for information on how to create your own snippets from the MSDN.  Personally I think the interface should have the functionality to just insert snippets of code like other add-in tools, however it is better than what we had previously off-the-shelf.

Figure 1 – Code Snippet Manager


This looks great but there is no insert button to put these snippets into my code, I tried.  So how do I enter the code into the query editor as I type?  Hold the control button (CTRL) and then press the K and then X to begin the insert, and yes I know this is an awkward key combination (what where they thinking).  See figure 2 and choose a category of snippets.

Figure 2 – Insert Snippet

After choosing a category then choose a snippet (see figure 3).

Figure 3 – Choose a Snippet

After choosing your snippet, it will be inserted into the query editor (see Figure 4).  At this point you can customize the snippet to fit your code and you are done!  Enjoy!

Figure 4 – The Finished Snippet




Duplicate Columns

Yesterday I needed to find several tables in a database that contained a certain column name. It is usually easy if you have only a few tables but this database has almost 2000 tables. I needed to use some T-SQL skills.
I consulted with the SQLHelp hash tag on twitter because it has been a while since I did this last and I knew someone there would remember how to do it quicker than I could. Robert Davis, MCM extraordinaire (blog |twitter) had the following line:

SELECT object_name(object_ID) FROM sys.columns WHERE NAME = ‘<column name>’ 

While Bill Fellows (blog|twitter) had a different approach:

SELECT TABLE_NAME FROMinformation_schema.COLUMNS ISC WHERE isc.COLUMN_NAME =‘<column name>’

Both worked very well and here they are here for you and for me when I need to remember how I did it.  Enjoy!

Fun With SSMS

Recently I was showing a friend a query in SSMS, cause that’s what geeks do.  I typed ‘SELECT’ and then dragged the columns over to the query window.  He said “whoa, how did you do that?”  Which reminded me that we all learn little things that we assume everyone else knows but apparently not everyone knows them all.  That is why I love blogging, because I can share and when I read your blog I can learn what you share.  Maybe I know, maybe I do not.  The SQL community prospers either way.

So here is the tip:  If you grab the ‘Columns’ folder underneath the table in SSMS and drag it across to the query window, then all of the column names are listed which is helpful when you do not want to type 40 of the 50 columns that you want for a particular query.  It is a nice little trick, but I wish it would just drag a handful or that it would insert brackets in there but beggers can’t be choosers.  Or can they?  Enjoy!

Color Coding SSMS

Finally got around to color coding my SQL Server Management Studio (2008) on my desktop today, which is a great feature if you haven’t set it up already.  Essentially it changes the color at the bottom of your query window which is usually a light yellow.  Why bother?

Well for me I set my production boxes to red, acceptance boxes to yellow, and development boxes to green to remind my brain whether it is safe to make changes to the environment.  Once you have a few query windows open, you tend to forget to which environment that window belongs to.  This is not foolproof, but it is a handy reminder to be mindful of your environment.

Not sure how to set it up?

Well using the registered servers feature, add your server and select the properties.  Then choose the connection properties tab and select the ‘use custom color’ option and then press select to choose your color.  Easy and remember to restart SSMS for this to take effect. Enjoy.

CodePlex Tool: SQL Server 2008 Extended Events SSMS Addin

Yesterday we discussed traversing the CodePlex tool offerings for SQL Server.  Our first installment is the SQL Server 2008 Extended Events SSMS Addin written by MVP/MCM Jonathan Kehayias (blog|twitter)  of

The install was a simple GUI installer and after an SSMS restart, I could see the nice little GUI as seen in the image below.  This is great idea because there is currently not an extended events interface in SQL Server Management Studio. 

Personally, I haven’t worked very much with extended events, but I would like to learn more.  With that being said, I am not sure if the tool works or not because I really do not know what I am looking for.  Does this image mean I have an error?  When I click on the event , it does nothing which leads me to believe that I do not have an error.  I do see where I can edit the event session “system_health” but I am not sure if the items in the list are always there or only there when the event fires?  Jonathan is an MCM and is much smarter than me in regards to SQL Server, so maybe someone else can comment on this tool?  Maybe we can convince Jonathan to create some documentation.  Enjoy!

Extended Events SSMS GUI

My Attempt To Install SSMS on Citrix….Pass (Part Two)

Yesterday I told you about my failed attempt to install SSMS for SQL Server 2008 R2 as a Citrix application and the failure that ensued.  My apologies for making you wait a day for the solution, but I was rushed with another production issue yesterday and decided to break this into two posts for dramatic effect.

Drumroll please!

 I found the following solution on several websites including Microsoft:

You need to change the following registry key locations:


Change the values of SP and SPIndex to 1 and SPName to SP1.  The original values in my case were 0 and RTM, respectively.  This is a strange issue because Visual Studio 2008 was never installed on this machine.

I hope this helps! Enjoy!

My Attempt To Install SSMS on Citrix….Fail

I had a goal in mind:  install SSMS on Citrix so that if I had to look at one of my SQL servers, I would not need to remote desktop into my desktop or the server itself, in case I needed to look at multiple servers.  I wanted just to set up SSMS on the Citrix server as an application so that I could quickly assess problems and avoid redecorating my three monitors when the icons get squished down into one RDP session.

 I downloaded the 2008 R2 SSMS installer from Microsoft instead of using my media.  After running the installer, I get the following error:

SSMS Install Error

 When I run Windows Update, there are no updates available for Visual Studio. 
Has anyone seen this before?  My Citrix administrator does not know how to proceed.  I have never seen this before.  Any ideas?
I will blog the solution tomorrow!  Enjoy!

SSMS Shortcuts

Below are some common SQL Server Management Studio keyboard shortcuts that I use on a daily basis to make my SQL life much more productive:

  • Ctrl-N…..Open a New Query with the Existing Connection
  • Ctrl-Alt-G…..Display Registered Servers
  • Ctrl-Alt-T…..Display Template Explorer
  • Ctrl-Alt-L…..Display Solution Explorer
  • F4…..Display the Properties Window
  • F8…..Display Object Explorer
  • F7…..Display the Object Explorer Details Window
  • Ctrl-F…..Find
  • Ctrl-H…..Replace




Get every new post delivered to your Inbox.

Join 1,080 other followers