What’s in your toolbox?

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