3 February 2018
3 February 2018

Customizing the SQL Prompt built-in snippets: a better ALTER TABLE ADD (ata) snippet

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

Snippets are a great feature of SQL Prompt. They save coding time, and introduce standards and consistency to the way you build code modules. They have multiple replacement points (placeholders) for parameters, and you can invoke them directly from an SSMS query pane.

SQL Prompt also comes with many useful built-in snippets, but sometimes we need to do some customization work, to add the functionality we need. As an example, how might we improve the ALTER TABLE ADD snippet, ‘ata‘?

The built-in ata snippet

Open the Snippet Manager from the SQL Prompt menu, and click on the ata snippet to see its code.

C:\Users\TONY~1.DAV\AppData\Local\Temp\SNAGHTML1b19178.PNG

This snippet seems designed more to reassure you with its simplicity than to impress you with its versatility. It contains two custom placeholders, $table_name$ and $column_name$, and two built-in placeholders $SELECTIONEND$ and $SELECTIONSTART$, to specify that the INT should be selected after the snippet is applied.

It gets you started, at least, but it doesn’t get you very far. Does the new column accept NULL values? Should it have DEFAULT value? A CHECK constraint, or a UNIQUE constraint? A COLLATION specification? How do we document the purpose of the new column? All of this is still manual work.

Of course, one can end up over-complicating the snippet to cover all possible cases. As a DBA, you will just want the snippet to enforce certain minimum standards, such as specifying whether the column accepts NULLs, and making sure the new column is well-commented and documented.

Customizing snippets using template parameters

It is a well-kept secret that you can use template parameters in snippets. SQL Prompt will call the Specify Values for Template Parameters function in SSMS, and when you invoke the snippet you’re presented with a nice SSMS form for specifying all the template parameter values.

When you’ve finished, the snippet will be pasted into the query window with everything filled in using the details you supplied in the form. You can even use SSMS templates directly as snippets if you were to add them to your snippet collection, because the parameters work as they always did.

The template parameters are enclosed in angle brackets (<>) within the snippet, in the format:

<parameter_name, data_type, default_value>

As far as I’m aware the data_type information is never used and can be safely left blank. The parameter_name can be a complete sentence that explain what you expect to be entered for that parameter. The default_value can itself contain a built-in placeholder. You can use the same parameter value in several places in the snippet, but the entire parameter block, within the <> delimiters, must be identical, otherwise it will be repeated several times in the SSMS form.

This allows us to be a lot more versatile. Listing 1 shows the code for my custom ata snippet.

Listing 1

Having edited the existing ata snippet to the new definition, we can write the name of a table into a query pane (or drag it in from SSMS Object Explorer), select it and then invoke the new ata snippet from the Actions list. You’ll be presented with the SSMS form to fill in all the parameter values.

Notice that use of the $DBNAME$ placeholder has filled in the database name, and the $SELECTEDTEXT$ built-in placeholder has captured our highlighted text as the table name. In this example, we had added the schema name but we have cut and paste that into the Name of the Schema value.

You can use other information such as the name of the user, or whatever is on the clipboard. You can now fill in the form with the correct details.

Then, just hit the OK button.

Listing 2

Not only do we have the code to add the extra column but, we also have in place, automatically, the rather unpleasant code to add the documentation for the column. This is also added to the source header. My name and the date has been added too, as if by magic(well via the built-in placeholders, actually).

As discussed earlier, we could take this snippet further, to account for the need to add several columns at once, add a column CHECK constraint, or a DEFAULT constraint, and so on. However, I see little sense in trying to complicate a snippet beyond the obvious, so maybe the best approach is to add a clue of the syntax, such as:

If you enclose these syntax hints with the placeholders $SELECTIONSTART$ and $SELECTIONEND$, then you can quickly delete them once you’ve reminded yourself of the syntax. Although I can keep the common syntax in my head, there is a huge amount that I have to look up so any cribs that one can get from a snippet would be useful.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

You may also like

  • Article

    How to record T-SQL execution times using a SQL Prompt snippet

    A SQL Prompt snippet is a pre-defined block of code, which we can insert at the current cursor position, or around currently-selected SQL text, in an SSMS query pane. DBAs use them to store all their most commonly-used checks and routines. It’s often useful to have your favorite troubleshooting scripts available to you within a

  • Article

    Consider using [NOT] EXISTS instead of [NOT] IN with a subquery (PE019)

    It used to be that the EXISTS logical operator was faster than IN, when comparing data sets using a subquery. For example, in cases where the query had to perform a certain task, but only if the subquery returned any rows, then when evaluating WHERE EXISTS (subquery), the database engine could quit searching as

  • Article

    SQL Prompt Hidden Gems: The SSMS Results Pane

    Within SSMS, when executing a query, we can view the query results in a grid, or tabular, format (Ctrl-D), as plain text (Ctrl-T), or we can write them to a plain-text reporting (.rpt) file using Ctrl-Shift-F. However, over my 20 plus years as a SQL programmer, I’ve often needed to take these results and use

  • Article

    Faster SQL Server troubleshooting using SQL Prompt snippets

    A good DBA will automate repetitive tasks, but sometimes you need to troubleshoot a problem ‘live’, with people standing over you, and the pressure on. This is something that can’t be automated. SQL Prompt snippets have helped me out in this sort of situation numerous times. Built-in SSMS query shortcuts SSMS provides a few shortcuts

  • University

    Take the SQL Prompt course

    This Redgate University course takes you from installation all the way up to getting the most out of the advanced operations in SQL Prompt. As well as autocompleting your code, you’ll learn how SQL Prompt can help you with code formatting, object renaming, code analysis, and other useful tip and tricks.

  • Forums

    SQL Prompt Forum

    Write, format, and refactor SQL effortlessly