I've written too many SQL procedures and functions to remember. This means that I'm in danger of rewriting perfectly good code I've forgotten. Then, there are other people's perfectly good SQL routines. On top of that, there are the nuggets of code that we all need only occasionally, but always in a hurry.

Over the years, I've seen several techniques for keeping tabs on code you need just occasionally. You know when it happens: you get a weird error about, say, a database having no owner. Eh? Oh yes, EXEC sp_changedbowner 'sa'. Or you need to free the cache, but who, while living a normal life, remembers obscure spells such as ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE?

We all need somewhere to squirrel this stuff away so that it is easy to retrieve. A friend of mine put all his favourite routines and functions on a website that he intended just for his own use. As he went from site to site, he was able to access his library. After a while it became very popular with other SQL developers. It is still there, raw and undocumented, but very useful. I've now published quite a lot of code and when I'm stuck with a problem, I often stumble across code that looks like it would do the trick, while googling for a solution. While wondering who wrote it, I sometimes see my own name. Really, there must be a better way.

I've tried many different tricks to remember code and archive old code for reuse, but I've yet to find the perfect solution. Templates and snippets have their place, but they aren't intended for dredging through your, or your team's, extensive back-catalogue. SSMS templates are fine for general syntax but aren't particularly good for team working, or for large volumes of code and neither are SQL Prompt's snippets. There is also the problem of running SSMS while hot-desking. I've tried network-based systems. They're OK, but you probably will want something like the search power of Prompt's Tab History.

One utility I've never abandoned, beside SQL Prompt, is Jan Goyvaerts's AceText. In fact, make that three inter-related utilities: AceText, EditPad and Regex Buddy. AceText provides for Windows the clipboard that it should always have had. It provides a huge, permanent list of the text you put on the clipboard, going back years, which you can search easily. You can also categorize what's on that list into other lists. You can edit the text, copy it, format it, save it, or print it. Assuming you remember to copy your routines on to the clipboard as you create or discover them, they are in the system. Once in the Acetext clipboard, you can tidy and sort them at your leisure. The problem, of course, is in making your snippets portable. It also can't extend into a team-based resource.

Nowadays, we can generally rely on internet connectivity, so we can use GitHub or OneNote. For teamwork, Github is excellent. My problem is that there are so many good options that I now have my code in lots of different places with no clear idea where the latest version of anything resides, and a maintenance problem when changing PC or hot-desking.

Should I feel guilty about archiving code, snippets, templates and so on in lots of different ways? I keep believing that there is a better way of doing it. Perhaps this is just another technological dream, just like reliable sentiment analysis.

Phil Factor