Click here to monitor SSC

Tony Davis is an Editor with Redgate Software, based in Cambridge (UK), specializing in databases, and especially SQL Server. He edits articles and writes editorials for both the and websites and newsletters, with a combined audience of over 1.5 million subscribers. You can sample his short-form writing at either his blog or his author page. As the editor behind most of the SQL Server books published by Red Gate, he spends much of his time helping others express what they know about SQL Server. He is also the lead author of the book, SQL Server Transaction Log Management. In his spare time, he enjoys running, football, contemporary fiction and real ale.

D.R.Y. with SQL Scripts

Published 28 March 2014 3:54 pm

Developers strive to write well-tested, reusable code with well-defined interfaces so that when they need to update the functionality, they need do so in one place only. It is the principle of ‘Don’t Repeat Yourself’ (D.R.Y.).

However, it is common for developers to be poor at applying D.R.Y. to their own past work. When it comes time to implement some complex new routine, a faint bell rings in their mind…didn’t I write something similar for that CRM project? What year was that…? If a brute-force search through their chaotic script archive doesn’t unearth it, within a few minutes, they then roll up sleeves, crack knuckles and set about writing it again from scratch, convinced they will do a better job of it this time, anyway: And, after all, it’s fun.

However, what if you really don’t have time to write the code from scratch, or need some pointers to get started? You might trawl a few of your favorite blogs, or find something on Stack Overflow. After all, many developers and DBAs blog all sorts of snippets and scripts, suggesting hopefully that they may be useful to others, but admitting that their main motivation is to know where to come the next time they need it themselves!

Another option, when in need of SQL code, is to search a public script archive such as the one on This always used to be a tricky operation. If, for example, we wanted a string-splitting function, we’d need to type into Google something like ‘ list split‘. Now it is so much easier, with the addition of a small SSMS plug-in called SQL Scripts.

Let’s say you’re browsing the daily SSC newsletter and spot a potentially useful script, but don’t have time right now to look deeper. Simply add the script to your SSC briefcase and, with SQL Scripts installed, you can access all the scripts in your briefcase directly from SSMS, from the “favorites” menu.Red Gate SQL Scripts for SSC

In addition, you can search the whole archive directly from within SSMS, as well as add scripts to your briefcase and to the archive as a whole.

It has the potential to be a very useful tool. At least, it’s a good start. I’d rather like the ability to filter scripts by tag, rather than just perform a blanket search. While sorting by “star rating” is somewhat useful, I’d rather like a more meaningful indicator of quality. Perhaps we should follow Phil Factor’s example; the SSC community could set up a minimal test framework, and test the top x scripts in each category/tag. Those that perform and scale adequately could get some sort of special marker in the archive. I’d also like an indication of the versions of SQL Server on which each script will run.

If you’re an occasional or frequent miner of the SSC script archive, we’d love you to try out SQL Scripts and let us know what you think. How much might it help you reuse the code of others, as well as find and reuse your own? What ideas do you have for additional features?

9 Responses to “D.R.Y. with SQL Scripts”

  1. DavidJackson says:

    Wow, thanks! I have an issue finding old scripts just like everyone else. Newer ones I have stored better, but older ones were done using a different methodology. The ability to have a location you can search for is obviously useful.

    Thanks again for pointing out a great tool that I was unaware of.

  2. Lee Dise says:

    It’s hard to believe I’ve been working for my current employer for close to thirteen years, but there it is. I was actually fairly new to SQL Server when I was hired on here as DBA, but I’d seen extensive service already in Sybase and (later) Oracle, so (as Joni Mitchell croons) I’ve looked at databases on both sides now.

    Early on here, a contractor suggested to me that I create a database on every server named ‘Utils’, and that it serve as the go-to place for procedures and functions that would help the T-SQL programmers. It struck me as a good idea, and now we have two such databases: ‘Utils’, for everyone; and ‘dba’, for DBAs. Developers, by the way, are free to add routines to Utils.

    Probably my most widely-used routine, by far, is a ‘dba’ routine I call ‘p_copy_table’, which does exactly what it says, namely, copy table contents between tables with compatible column definitions. We’ll probably be retiring it soon, now that we have Powershell and SSIS. My erstwhile colleague, Brian (who left for greener pastures about a year ago), was not a fan. His remark: “Every time you use that routine, an angel loses his wings.” It has been useful, but it is certainly not elegant.

    • paschott says:

      @Lee, just wanted to say that you’re not alone in routines like your p_copy_table. I love the quote on angels losing their wings. I’ll have to borrow that one. We’ve had several parts of our code base that fit that criteria.

  3. Sergio E. says:

    What a wonderfull tip, I didn’t know this tool but thanks to you I’ll have another addition to my “virtual toolbox”

    Best regards,

  4. JonRobertson says:

    I’d love to check out the SQL Scripts plug-in. But the link isn’t working for me. I tried a Google search and SQLServerCentral’s search and still couldn’t find it.

    Anyone have a working link? ;)

  5. paschott says:

    Installed it the other day and gave it a quick look. I remember some other stand-alone tool that had tried to do something similar, but having it right in SSMS is very convenient. Tying it to our SSC login is great because it gives us both the scripts we’ve saved as well as access to other scripts via search. I may not use it too often, but when I have a need it will be very handy to look up scripts within my most-used tool. Thanks for bringing this to our attention and integrating it with SSMS.

    I agree about tagging with SQL Server versions (and possibly editions) as well as a good rating system beyond the star system used now. It would definitely be easier to tag and rate scripts through a tool like this rather than through the SSC website.

  6. Keith Rowley says:

    This looks really cool.

Leave a Reply

Blog archive