Category: SQL Server

I’m setting up my new work laptop, and decided to have a look around the net for what the cool kids were doing these days as far as customising SQL Management Studio 2012.  Surprisingly not a lot seems to have changed (i.e. changing font, adding keyboard shortcuts, and using a trendy dark theme are still the most common tweaks).

Aesthetics

So I found a dark theme that I actually might give a chance (I’ve tried a few, but always seem to revert to the default after a few hours).  I’m still not sold on the dark development environment – but there are certainly enough colour combinations available from sites like http://studiostyl.es/.

Productivity

I also added my usual battery of keyboard shortcuts; sp_helptext, sp_whoisactive, and a wrapper script that I unimaginatively call sp_helpindex2 (which auto-detects the version of SQL running, and then calls the corresponding version of Kimberly Tripps improved sp_helpindex proc).

Some other minor tweaks include scripting options (Tools -> Options -> SQL Server Object Explorer -> Scripting), for example adding descriptive headers, checking for object existence, etc.

There are loads of other tweaks you can make – do yourself a favour and take the time to read through every page of options… I’m sure you’ll find more ways of improving your environment.

Templates

I then needed to get my templates setup (which, as I detailed in my previous post, I use to store frequently used scripts and code snippets).  This takes a little jiggery-pokery since SSMS doesn’t natively let you specify a new location for these files.

What you need to know before starting is how Management Studio handles these template directories; in the following default path, C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems, there will be a directory called “SQL” which contains all the default templates.

When you open the templates pane in SSMS it compares the contents of this directory to your user template directory (e.g. C:\Users\username\AppData\Roaming\Microsoft\SQL Server Management Studio\11.0\Templates, or explorer window #2 in the screenshot above), and “syncs” up any differences.  I’m sure this is over-simplifying it, but it’s a good-enough explanation for the purposes of this post. Window #3 shown above is the directory containing my custom scripts library in my Sky Drive folder.

So what you want to do is automagically “replace” the contents of #1 with #3 – SQL will take care of keeping #2 in sync (I hope…).

To do this you need to create a symbolic link from #3 to #1.  To learn about symbolic links, read this.  They are essentially shortcuts… but on steroids.  Here’s the command I use to setup my environment:

cd "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems"
ren Sql Sql_Old
mklink /D Sql "C:\My Stuff\SkyDrive\Scripts\SQL\SSMS_Templates\Sql"

Obviously your paths (and username) will differ, so make the appropriate changes before trying this yourself.  Please be sure about what you’re doing (never just run something from the Internet without 100% understanding what it does). I take no responsibility if you somehow manage to brick your system – YMMV, etc, etc.

All going to plan, you’ll now have SSMS using your own source of templates. 🙂

Cheers,
Dave

I’ve had a few weeks off work recently, during which time I decided to tidy up (or attempt to anyway) my collection of “utility scripts”.  This consists of about 95% regular T-SQL scripts, with the rest being a mixture of PowerShell scripts, batch files, etc.

This made me wonder how other people managed their collections, and whether their are better ways (or tools) to catalogue and easily retrieve useful scripts and snippets?

What I do

My solution is to use the built in SQL Server Management Studio Template Explorer.  I found a few good blog posts explaining where the templates are stored, and some different ways of getting the most out of them (for example, I went down the route of creating symbolic links so I could have one central set of scripts stored on my Skydrive, which I have access to from any computer).

I was thinking about taking this one step further; I’m the database team lead at work, and it might be useful to collaboratively compile a list of scripts that each of us find useful/indispensable, which we could then add to our source control repository.  We could then each have symbolic links to this repository on our own computers, meaning the whole team would automatically have access to the latest versions of our “über team toolbox” scripts.

What’s in my toolbox?

I’ve got a bunch of stuff… and I’ve still got a lot to categorise and move into my templates folder, but it’s a mix of some pretty well known utility scripts from some equally well known “SQL personalities”, as well as a lot of stuff I’ve written over the years and tucked aside for a rainy day, or scripts that I regularly need for the applications that we support.

My “well known” utility scripts currently include:

• Aaron Bertrand’s more reliable sp_MSforeachdb
• Adam Machanic’s indispensable sp_whoisactive
• Brent Ozar’s comprehensive sp_Blitz and sp_BlitzIndex
• Ola Hallengren’s automated maintenance “solution”

I have others that fall into this category (e.g. some queries from Glen Berry) that I’m still tidying up and organising.  The SQLServerPedia site also has a list of useful scripts which I’ve bookmarked for later follow-up.

What else is there?

Well, if you don’t think using templates is going to work for you, then there are a few other options (although not as many as I would’ve thought – hence the reason for this post)…

Snippets

I think of snippets as mini, or “in-line” templates.  The reason being most snippets are fragments of a larger script – for example you can have a snippet to insert a common stored procedure header block that you always use, or the syntax for a cursor declaration.  My templates tend to be more full-featured scripts, but I’m sure these could be stored as snippets too.  Here’s an article on snippets, including how to create your own.

Talking about snippets – it’s easy to confuse the built-in SSMS snippets (which is what I was talking about above) with those included in Mladen Prajdić’s very cool SSMS Tools Pack add-in.  In SSMS Tools Pack they’re more like fully customisable shortcuts to code snippets.  This means you don’t need to have an extra window open to select the snippet, you can just (for example) type ssf and hit enter, and it expands out to SELECT * FROM automagically. 😉

SQL Scripts Manager

This is a free tool from Red Gate, which to be honest I haven’t played with very much yet (it’s still on my to-do list).  It seems to offer some cool features (like tagging scripts, a list of authors, etc), and comes bundled with a range of very useful scripts to get you started.  Here’s a write-up on it.

I’ll definitely take a closer look at it when I get a chance – I’m interested to see where it stores the scripts (i.e. whether I can plug it into a central source control repository), and also whether there is any SSMS integration (which would be way cooler than just an external app).

Good ol’ Explorer

I’m guessing this is still the most common method; just putting a bunch of snippets and scripts into a directory (or, if you’ve got some semblance of order, perhaps a categorised series of directories).

If this works for you (as it did me for many years), and you can still easily find what you need, then I guess there’s no real reason to change. 😉

What do you use?

So, what method do you use to keep your scripts library in order, and do you have any other cool scripts you’d recommend I add to my library?  I’d love to hear from you via the comments section below.

Cheers,
DB Dave