• SQL Prompt
• SQL Code Snippets

Sharing SQL Code Snippets with Coworkers, The World and Yourself

Managing different sources of snippets

As a career database programmer/architect, a technical writer, and a hobby photographer, I use a SQL Server database, and write SQL, for many reasons. I use code snippets pretty much every day, and for all these activities.

Our team use a shared set of “corporate” snippets for managing, querying, and troubleshooting our databases. For example, we have a code snippet to create tables and columns using our corporate standards, and another to make it easier to drop a table or column and there are dependencies. We also have snippets to help us explore the structure of tables in our databases, troubleshoot locking and blocking, validate configurations before a release, and more. The latest version of each of these ‘corporate’ snippets must be available in a central location, to which everyone in the team has access. Anyone in the team can propose changes but they must always be fully tested before I or any team member distributes or changes a snippet.

I also maintain a set of personal snippets that I use for formatting SQL comments for books or articles, getting min and max datatype values, and so on. They are general-purpose enough that I have them published on GitHub here: (https://github.com/drsqlgithub/sqlprompt/tree/master/snippets).

Finally, I have a set of snippets for maintaining my ‘hobby’ databases, such as my photography database, which I want to save in source control alongside the database source code. I don’t share these snippets, simply because they are just ‘noise’ to the anyone but myself.

My goal is to be able to keep the different sources of snippets in one library, but also be able to source control them separately and “publish” only those that I want to share. However, SQL Prompt only one directory in which to store your snippets. Another concern is that I work on at least three different computers regularly, and I want to have my snippet library available for my use on all of them, and a simple local shared folder will not do because they are not on the same network.

Source separation through naming conventions

Like many programming problems, a big part of the solution for distinguishing between different types of snippet is in having a consistent naming convention. Each of SQL Prompt’s built-in code snippets is generally very short and simple, and is invoked using a 3-4 letter shortcut code, which is an initialism of the SQL command it executes (so ALTER TABLE…ALTER COLUMN is atac).

With some thought, this shortcut convention could be extended to cater for different sources or types of snippet. You might use the first letter to denote the category of a snippet – for example m for a metadata snippet, d for DDL and q for queries, and so on, followed by two or three letters to denote the purpose of the snippet. For example, the shortcut code for a snippet to get the definition of any missing indexes could be mixm or even mxm, or to find out how indexes are used, mxrw (index read writes).

However, as a database programmer and architect, I have always been at the other end of the naming spectrum and prefer to use self-describing, verbose, Pascal-cased names. So, if the snippet’s output is a query to list the databases on the server, I would name it DatabaseList, and my “drop column” snippet is called, you can probably guess, DropColumn rather than dc.

The way I distinguish between different sources of snippets is to use prefixes:

• C_ is a shortened version of our corporate prefix, which is three letters long
• LD_ denotes my personal snippets that could be useful to others
• TW_ are my hobby database snippets that are only useful to me
• No prefix means it comes from another location, such as from Redgate’s built-in snippet directory. If we added it to our corporate items, it would get a prefix and a comment as to where it came from.

If I want to see a list of all my personal snippets, in SSMS, I can simply type the prefix (perhaps including the underscore, depending on the complexity of the prefix):

Alternatively, I can simply start typing the non-prefixed name of the snippet:

If you have a lot of pre-existing snippets, and you want to standardize names or add a prefix, you are going to need to rename, then this can be a pretty tedious task if you going to do it using the Snippet Manager, one at a time.

I find it easier to use a text editor and directly edit the XML of the snippet. To rename the snippet, you cannot just rename the file as will not change the name of the snippet internally: you must change the XML as well. For example, this snippet is one I built to fetch when the server last restarted:

Note that while you can have the name of the file different from the Title and Shortcut, the Snippet Manager editor currently uses the same for all three, so be careful if you do that when you are making changes to snippets.

Sharing and Source Controlling Snippets

If you are the only user or developer of snippets, using a single computer, maintaining snippets is a straightforward task. SQL Prompt gives you one directory to link to, and you can then store and edit all the snippets in that directory. By default, snippets are held in an AppData folder. On my local machine, they are located here: “C:\Users\drsql\AppData\Local\Red Gate\SQL Prompt 10\Snippets“. However, you can change that directory very simply, by opening SQL Prompt’s Snippet Manager, and changing the location of the snippet folder:

You can see a bit more detail in this Redgate document. Of course, you will need to copy the Redgate Snippets to the new folder for them to be available to you.

Limitations of Using A Shared Folder

One of the simplest methods of sharing snippets within a team is to change the location of Prompt’s snippet folder to be a network share (one that, ideally, is regularly backed up). If you need to accommodate users working on multiple machines and on different networks, you can use a folder on a file share service like Dropbox or OneDrive.

However, this technique has one serious drawback: making controlled changes. As your team realizes the wealth of code that can be embedded in the Prompt’s snippets library, they will almost certainly start using them, writing new ones, and eventually, start modifying snippets they think aren’t coded in the best possible way. While this is absolutely to be encouraged, it can cause problems using a typical shared folder, because any change to the snippet is immediately propagated to all the other users. This means that while one user is the middle of altering a snippet, it could very well be temporarily broken for all other users.

Also, what happens when the change is very wrong? Consider you have a snippet as follows:

And then a developer changes the snippet code to something a lot more destructive, while they are testing locally:

Another user uses the snippet without looking at the code, which is how we should be able to use a good, tested snippet, and data is gone. OK, this is an extreme example, but sometimes developers do test out a new pattern that appears to work fine on their dev instance, only to discover that it causes problems for others.

Using a Source Control directory

Rather than have code snippet changes propagated immediately to all other developers who use the shared folder was their snippet library, it is almost always better to use a more controlled sharing mechanism. The obvious answer is to use a source-controlled directory as the Snippet folder, allowing us to push and pull changes to and from a shared repository. By using pull requests, we can also introduce code reviews, document changes, and so on.

GitHub provides a free, online source control repository that is easy both to set up and to manage. Using their companion GitHub Desktop, you can check in to directory, and it’s subdirectories, in a repository that you can keep private, share with selected users, or even share with everyone. I have several repositories on my GitHub page here, including the aforementioned snippets.

For our corporate snippets, we have a source-controlled directory in Team Foundation Version Control in Azure where we managed our team (C_) code snippets:

Any member of the team can add new snippets, change existing snippets, test them, and request a code review. Each user can also issue “get latest” to get new changes from the shared repo. This makes it less likely that other use will start using get half-baked snippet code without realizing it isn’t ready.

Sharing and integrating multiple snippet sources

Setting their snippet folder to the TFS Source Control folder works fine for any users who just have a single source of work snippets. However, since I am trying to maintain several sources of snippets from a single library, I needed more control over the folder where the master copy of items is located. I also needed a way to share work snippets with my co-workers, general purpose snippets with everyone, and hobby snippets with no-one.

• C_ – Corporate Snippets: source controlled in corporate source control
• LD_ – Personal Snippet Library: GitHub, in a public repository
• TW_ – Hobby database: strictly in a Dropbox folder.

Here’s the scheme I introduced. I warn you up-front that it’s not perfect and it can still get messy, but luckily once you have your snippet library established, you will likely make only a few changes.

On all my computers, I use a shared Dropbox folder as my snippets folder. This folder is shared only with myself, to allow me to make changes on one computer and have them show up on all my computers. In it, I keep a few batch files to copy files of different types in and out of the folder into the correct source control folder, as follows:

When I make a change to a corporate snippet, I test it locally, then on my corporate machine I copy the file to a Dropbox folder that is linked to TFS. I can compare to previous versions, and then check the file in for others to code review and test.

For my personal snippets, I do the same thing with a Dropbox folder that is linked to GitHub, though all the code review is done by me alone. I use a PowerShell script to copy all the items with a LD_ prefix to the GitHub folder:

Then using GitHub Desktop, I can compare differences in the XML of the LD_ snippets and check changes into the repository. For the items I use out of the SQL Prompt library, I just copy them to the Dropbox directory, and they are available for use as well.

Summary

Of all the features I have used in SQL Prompt, snippets are the main reason I use the tool pretty much daily, other than for JOIN criteria completion, because that is essential! As such, I needed a better way to integrate and distribute multiple snippet sources. My solution is a little messy, but it works.

By establishing a naming standard and a set of folders for maintaining history of the snippet code, I can share changes with myself, my coworkers and, through GitHub, the whole world. Using both source control and file sharing services help with fear of losing code if someone overwrites or deletes a snippet, because it maintains detailed version history in multiple places. GitHub and my corporate source control give us a history based on check-ins of known, good code. It also doesn’t hurt that Dropbox provides me with a way to revert to any version of a file I have saved in a synchronized folder, eliminating the fear of overwriting code that I am in the process of testing.

Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly

Find out more