Customizing the SQL Prompt built-in snippets: a better ‘ata’ snippet

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.

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.

/**
Summary: >
  Add the column <Name of the new column, sysname, MyColumn> of datatype <datatype of this column,, datetime> with the option of <NULL or NOT NULL (allow nulls?),, NULL>
  to the table <Name of the Schema, sysname, dbo>.<Table name, sysname, $SELECTEDTEXT$> on <database, sysname, $DBNAME$> (<What is this column for?, sysname, unknown>)
Author: $USER$
Date: $DATE$
Database: <database, sysname, $DBNAME$>
**/
USE <database, sysname, $DBNAME$>
GO

-- Add a new column to the table
ALTER TABLE <Name of the Schema, sysname, dbo>.<Table name, sysname, $SELECTEDTEXT$>
    ADD <Name of the new column, sysname, MyColumn> <datatype of this column,, datetime> <NULL or NOT NULL (allow nulls?),, NULL> -- <What is this column for?, sysname, unknown>
GO
EXEC sys.sp_addextendedproperty 
@name = N'MS_Description', 
@value = N'<What is this column for?, sysname, unknown>', 
@level0type = N'SCHEMA', @level0name = <Name of the Schema, sysname, dbo>, 
@level1type = N'TABLE',  @level1name = <Table name, sysname, $SELECTEDTEXT$>,
@level2type = N'COLUMN', @level2name = <Name of the new column, sysname, MyColumn>;

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.

/**
/**
Summary: >
  Add the column MobileNumber of datatype VARCHAR(14) with the option of NULL
  to the table Sales.Customer on AdventureWorks2012 (Contains the mobile number of the customer)
Author: PhilFactor
Date: 01/02/2018
Database: AdventureWorks2012
**/
USE AdventureWorks2012
GO

-- Add a new column to the table
ALTER TABLE Sales.Customer
   ADD MobileNumber VARCHAR(14) NULL -- Contains the mobile number of the customer
GO
EXEC sys.sp_addextendedproperty 
@name = N'MS_Description', 
@value = N'Contains the mobile number of the customer', 
@level0type = N'SCHEMA', @level0name = Sales, 
@level1type = N'TABLE',  @level1name = Customer,
@level2type = N'COLUMN', @level2name = MobileNumber;

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:

/* ALTER TABLE my_Table ADD my_Column INT (NULL or NOT NULL) CONSTRAINT my_Column_key (UNIQUE or PRIMARY KEY) REFERENCES Another_table(key_column) CONSTRAINT my_Column_Default DEFAULT 0 WITH VALUES */

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.