Phil Factor

10 March 2023

• SQL Prompt
• SSMS Tab Management

SQL Prompt Safety Net Features for SSMS: SQL History

We’ve all been there. I recall one such incident vividly. I was working at Redgate’s offices, and a passing developer laughed at my howls of rage, as SSMS crashed on me, just when I had almost finished a particularly clever stored procedure. A short while later, a higher entity made it happen to him too, so after reflecting soberly for some time, he developed SQL Tab Magic, in a “down-tools” project. It became a cult tool and eventually went mainstream, as part of SQL Prompt.

The “Tab Magic” features were, somewhat more prosaically, named Tab Color and Tab History and then in SQL Prompt v10.13, the latter was given a makeover and renamed to SQL History.

I’ll describe a few common mishaps that everyone working in SSMS will have experienced a couple of times and how SQL History provides ‘insurance’ against their consequences. If you’re still on a ‘Tab History’ version of SQL Prompt, you still have many of the same safeguards, but the search facility when hunting for code, is much more limited, and you won’t be able to rewind queries “back in time”.

For details of the Tab coloring safety net, see: SQL Prompt Safety Net Features for SSMS: Tab Coloring.

Recover from unexpected crashes

Just occasionally, SSMS disappears. You’ve been busy and have plenty of query tabs open. Suddenly, you don’t have SSMS anymore. You were minutes away from finishing some code that you’d laboured on for hours. Now you are, it would seem, still hours away. I managed to do it while writing this article.

There was a time when I consoled myself with the thought that I’d probably write it all better second-time around, but then there is the tedium of having to do so. My brain slowly empties overnight like a leaking whoopee cushion, so it means staying late to redo it before I forget it all.

SQL History was developed with characters like me in mind. Now, I just give a smug smile and hit the Prompt SQL History button in the Redgate toolbar in SSMS (View | Toolbars). I feel rather like that insufferable family in the ads who, on returning home to find their house submerged, merely smile ruefully, call their incredibly accommodating insurance company, and shortly after are all laughing merrily around the dinner table.

With SQL History, you have the equivalent SSMS insurance policy. If SSMS crashes, or if you just close down SSMS recklessly without saving your open tabs then, on relaunch, SQL History will restore all the tabs that you had open when SSMS was last alive. You’ll have the same work as you did when you last saw it. The application saves all the T-SQL code, just as it was before you closed or lost it. SQL History can also reconnect the tabs to the databases to which they were previously connected.

SQL History should be enabled by default, but you can verify this, before disaster strikes, by visiting SQL Prompt’s Options menu, and then click in Queries > History. Here, you can also limit the number of open queries you want SQL Prompt to restore, to avoid the feeling that you’ve time to make a cup of tea, each time you open SSMS.

With SQL History enabled, you’ll also have protection against the consequences of a few other common mistakes…

Retrieve carelessly discarded code

Tabbed query panes proliferate. Suddenly, you yearn for less clutter in your life, and start furiously clearing them out, all with default names like SQLQuery25.sql. Click on the x in each tab in turn. Save changes to the following items? Nope. Click – nope. Click – nope…

“Oh no, wait! On of those tabs I just shut had that brilliant stored procedure I spent ages working on, and now I’ve deleted the query pane without ever saving the file. All that work gone!” No, it hasn’t. SQL History has saved what I was working on. Click on the SQL History icon at the top of the SSMS application menu bar:

The left-hand pane displays a list of all the code you’ve been working on recently. The query list shows the file name, if you ever saved it, the date and time the query was last modified, and the SQL Server to which the tab is or was connected, and the environment to which that server belongs, as defined by your SQL Prompt Tab Color configuration. A vertical blue bar to the left of any query in the list indicates that it’s currently open.

Just click on each of the queries in the listing, or use the up and down arrows, and the code for that tab will appear in the right-hand pane. At the top right of the code preview pane, you’ll see the time the query was last modified, and at the bottom left you’ll see the database, as well as the SQL Server to which it was connected.

Using the icons just below the search bar, you can filter the list to only “starred” queries, only open queries, or only closed queries. To find code you accidentally closed without saving, filter the list to only closed queries, if necessary, and you should quickly locate it, since the list is ordered by time of tab closure. You can also, of course, just type in a simple search term.

Once you’ve found the code you want, double-click on it, in the query listing, and the lost query will be restored as an open query tab, just as you left it. Now save it, of course.

You might get the urge, while you’re here, to de-clutter the SQL History. Click on the SQL History button to get back to the original screen. Then, as you hover over each query in the list, an ellipsis appears containing options to open or rename a query, as well as to remove the query and its history, or remove all queries older than the current one.

Undo errant changes

The History window, below the main list of queries shows the change history for the selected query in the list, with timestamps for each version. This allows us to rewind a query back in time and is useful for those times when perhaps you can’t work out where or how you introduced a bug, or to correct an accidental or misjudged alteration:

Avoid reinventing the invented

I’m always doing it. Crikey, three weeks ago I hit a similar problem. I had a great solution in the end, but it took ages to develop. Now where is it?

SQL History, thankfully, has a versatile search function. The first task is to think of a good search term. Hmm. It was function to split strings, probably called something like “String Splitter”?

Open SQL History again. I like my SQL history pane as large as the query pane when I’m trying to find what I’ve been working on. Type into the search bar anything significant you can remember about the ‘lost’ code. In this case, I’m pretty sure the name had “split” in it, so I’ve done a wildcard search on that term, using the name: prefix to search only query names:

Similarly, you might be trying to dig out some old code for importing a CLOB, a JSON or text file, into SQL. Perhaps all you can remember it was an OpenRowSet command, so you can simply type in that term, or perhaps limit the search to the query content only, using the search term sql:openrowset.

In this case, I’ve gone straight there. The search term is even highlighted in Yellow Ochre. Click on the list item to read it into a query pane. You can hit Tab, to switch the context to the query preview pane (Shift+Tab to switch it the other way), and then Ctrl+A and Ctrl+C to copy the code onto the clipboard (only for saved queries).

If you click on the Advanced search link, you can limit the search results to a defined period, a particular server or database and more. The Search SQL History page in the documentation has all the details, including how to apply the same search filter using prefixes.

Summary

In an ideal world, you might think that you would be unlikely to need SQL History because you would be saving your work in source control dutifully on every change, and doing nothing wild, woolly or likely to lead other team member to raise their eyebrows.

In reality, SSMS is pretty undiscriminating in the way that it occasionally clicks its heels and vanishes, usually when you least expect it to. SQL Prompt SQL History deserves its cult status because it was designed for those of us that know better but occasionally cut corners. It tames SSMS and prevents us from making fools of ourselves. I was a very early adopter of “Tab Magic”, and I’ve lost no work ever since and even occasionally reuse things I’ve done before!