Cold Turkey with SSMS

Someone came up with the laughable suggestion that I ought to stop using SSMS for a fortnight just to see how I’d cope. He turned it into a challenge. With some trepidation, I accepted.

I coped fine. In fact, the more I did the more I enjoyed myself.

There are plenty of alternative IDEs for managing SQL Server, and some are remarkably good. I decided it would be cheating to use an alternative IDE. Besides, the trouble is that they all tie you in with a particular way of working, and sometimes you are not conscious of it. I wouldn’t consider permanently abandoning SSMS, particularly the rejuvinated version with SQL Server 2008 that even displays GIS data. However, complete dependency on SSMS is another matter entirely.

I use SQLCMD when it is all I can get my hands on, but could one, I wondered, use SQLCMD instead of SSMS? As a stand-alone console for SQL Server, it is a bit too primitive for me. I like being able to run selected code. I like to save versions of code, have stores of favourite tried n’ tested routines, and be able to do effective cut ‘n paste. But I appreciate things nice ‘n simple.

Every programmer who is still capable of independent thought has their own favourite programmers editor which they use when not forced to use Visual Studio. I decided to use my favourite programmers’ editor Editpad Pro. This is because its Regexp support is excellent, it is easily configured to ones exact requirements, and it is reasonably cheap. Once you’ve set it to the task, it makes life very easy. Also, the great Jan Goyvaerts ‘eats his own dog-food’ in that he uses Editpad to develop his own products. This means that there is generally a way to do ‘programmy’ things. Compilers, and filters such as HTMLtidy, can be ‘installed’ with EditPad pro so that, for example, you can use it to create and maintain C# CLR routines for SQL Server with very little effort. For the odd task, it is easier to use this than to crank up the full obesity of Visual Studio, especially if you have NET Reflector as ones torch into the darkness of the net assemblies. Also, as I tend to be diving between XML, HTML, VB, batch file, PowerShell and SQL Server, I decided that it made sense to see if it was possible to do it all from the same editor.

It hadn’t actually occurred to me that it was possible to use it for developing SQL Code. The first problem was to be able to execute stuff and get the results back. Editpad pro would have to execute a batch. By piping the contents of the edit window into SQLCMD, and getting the result back into a second window, this proved very easy. I could just execute selected text or the entire file as a batch, and include all the SQLCMD extra features. I had to use windows authentication, and I had to start off by hard coding the server and database. Fortunately, you can pass any elements of your current path to the batch so I arranged my directories to correspond to the server, instance and database. Once I had done that, I could tweak a DMO routine to save the database into the required directory for all my databases. As long as I was logged in via windows authentication, I was suddenly able to access all my databases without palaver. I then put every database as a separate project so it could search, open or spell-check all the files together.

Figure 1. The basics in place, and a clip library in the left pane

Next, I wanted the same colour syntax highlighting as I’m used to. With EditPad pro, it was a simple matter of prodding a few options in the Preferences dialog box, and all was looking better. I selected the ‘visual studio’ colouring scheme and changed the colours of function names, operators, reserved words and so on.

What about all my libraries of clips and templates? For templates, I already use the prototype SQL Wiki that manages, and executes, these in a browser. Clips are a speciality of Editpad; they are rather like snippets in SQL Prompt in their concept, but they are very easy to use. It was very quick to get libraries of favourite clips of SQL together.

Figure 2: A result in the result pane, and all source files for the project generated via DMO.

The next thing was to get the source of my database into the directory structure I’d set up. This was the work of a moment using the stored procedure I wrote and published a while back on Simple-Talk. The spScriptObjects procedure worked with no modification, putting all the source files into the directory. (Set up automatically, of course with a function key.) I could then put them all into the Editpad Pro project I’d set up so one could search them or edit them

I could go on and on. After a while I allowed myself to go back to using SSMS. I found that I could still use both together. The nicer aspects of the object browser drag and drop still work. Drag and drop even works from the Template explorer. Once I’d got used to all the extra features in the Programmers editor I was loth to stop using it. Also, every day I discover new features that make my life easier, and ways around the things I initially missed when I stopped using SSMS. There are some wonderful features in the command-line version of SQLCMD that aren’t available in SSMS, and they are fun to explore.

What did I miss the most? Query results to grid, Macro substitution in templates (SQLCMD probably does equivalent stuff), the easy point and click for admin tasks such as setting up jobs, Add-ins such as SQL Refactor. But then, there were so many compensations.