What’s in your toolbox?

Toolbox Lock - by Jenn DurfeyI’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?

My SSMS TemplatesI’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:

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

2 Responses to “What’s in your toolbox?”

  1. JulianLWoodhouse
    October 20, 2013 at 21:28 #

    Hi DB Dave,

    Nice post! Currently I’m new to the DBA sphere, working for relatively new startup that is still actively building the dba/db dev team and introducing policy on code management.

    Prior to recently our Tool box has been distributed between different members file structures broken down to projects as well as database types (MSSQL, Oracle, Teradata, SybaseIQ,PostgreSQL)

    We are currently making a push to get these scripts into a svn structure which works well for versioning but is restrictive on a single organisational tree.

    Another method we have used is the functionality from jira/ confluence which ties together nicely especially in regards to documenting our knowledge base..

    Lastly and idea I’ve been toying with is google drive which enables files to be stored once but navigated to under several ‘file structure’ navigations. So one script that is used for multiple clients can be present in all branches, as well as through the initial creator tree and lastly the database type.How ever I can see this taking more effort to maintain.

    Kind Regards,
    Julian Woodhouse

Trackbacks/Pingbacks

  1. Customising SQL Management Studio 2012 - database dave - April 22, 2013

    […] 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 […]

Leave a Reply