SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Avoiding the Hell of having to Remember DDL syntax

By Phil Factor,

I cut my teeth on SQL in the dark days of the late eighties when one had to tap code into a text-editor screen. Old habits die hard, and I've generally been a late adopter of such niceties as SQL templates and snippets. When all you have is a blank screen and a badly-indexed printed manual you soon develop pretty good coping mechanisms, and once you find something that works well, you cling to it with white knuckles. For a long time, I kept a directory of minimal build scripts for each type of object and common routine, but with iSQL and the other unfriendly SQL editors, even that is painfully laborious.

SSMS, when it replaced Query Analyser, was a revelation. Hmm, templates. Just drag 'n' drop and the template was suddenly there. One quick keystroke later and you had a form to fill in with the important details. Fill in the form and all the embedded macros have values in them. At the time, it seemed dead clever but there were some obvious things missing.

For example, there's no way to take a query and turn it into a view, function, procedure, trigger or spExecuteSQL batch. You need to be able to select your query and pick the template and have it put the selected text in the body of the function. Also, there is no way to put default macro values in automatically for headers, or inline comments; values such as the date and logged-in developer, or database-wide values such as the database name.

Also, with small screens, you tend to want to hide templates away to give yourself more real estate. It was this last factor that seems to have led to the SSMS snippets. Just right-click and you've got a cute menu of SQL objects that you can inject as build scripts into your query window at the insertion point. Good? No. you can't modify them or share with your team, and they have no macros.

I got bored with both templates and snippets and used a programmer's clipboard called AceText instead. It saves everything you put on the clipboard and allows you to access it retrospectively. It allows you to create and share special collections that have all your favorite, specialist stuff, which you can then modify. In terms of effort, this is as close to zero as you can get, especially since you can save templates retrospectively. Although it works fine with SSMS, and any other IDE or programmer's editor, it doesn't allow the 'Surround with'/'before-and-after' and the rectangular block facilities that are so useful, unless you use AceText's accompanying text editor, Editpad Pro.

Now that SQL Prompt's new formatter works so well, I'm back using that in SSMS. The snippets in Prompt have always been a bit of a grey area for me but I've been trying them out, and they certainly seem to work well. My only problem now is that I have three different ways of accessing snippets in SSMS, two native and one provided by SQL Prompt, and I'm still using AceText too. An embarrassment of riches.

What's your favorite way of keeping your productivity going strong without excessing 'googling?' I'd be fascinated to know.

Phil Factor.

Total article views: 79 | Views in the last 30 days: 1
Related Articles

SQL Prompt Tips #3 - Snippet Placeholder $Paste$

Another quick tip for SQL Prompt, using the snippets to increase your productivity.


Using Code Snippets in SSMS

Snippets will allow you to code faster by inserting chunks of code with few key strokes.


Using Query Analyzer Templates

Greg returns with an article based on some practical experience using QA templates to save time and ...


Best Editor To use

Best Editor to use for TSQL