SQL templates

SSMS has a very handy feature in the Template Explorer (Ctrl-Alt-T) that contains 100's of templates for all sorts of scripting tasks you might require:

• Open the Template Explorer
• Drill-down to the category
• Right-click on the desired task and click Open.
• Press Ctrl-Shift-M to display a popup screen & enter your parameters
• Click OK

You can also create custom templates:

• Right-click on the top node, select New ~ Folder and give it a name e.g. Custom
• Right-click on the Custom folder, select New ~ Template and give it a name e.g. MyTemplate
• Right-click on MyTemplate, select Edit
• Observe the Connect dialog & connect to the your server
• Type in the template code and click Save

. . .but when you do that, the default location is something like

C:\Documents and Settings\Owner\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\Templates\Sql\Custom

so you will probably want to use Save As... and store your custom templates in a subfolder closer to where you keep your regular scripts & apply source control. NOTE: Your template file should also be set to read-only, because when you do the Ctrl-Shift-M the displayed file name does not change, so you want to protect your templates.

One basic example of using a template is to add a new column that has a default value.

Here is a sample hard-coded script:

ALTER TABLE dbo.Company
ADD Flag bit NOT NULL
CONSTRAINT DF__Company__Flag
DEFAULT (0)
GO

You could of course create a simple template where you just search / replace ttt = table name, ccc = column name, etc. but it gets very cumbersome:

ALTER TABLE dbo.ttt
ADD ccc ddd NOT NULL
CONSTRAINT DF__ttt__ccc
DEFAULT (vvv)
GO

This is a template that works with SSMS:

important note - blogger does not display less-than or greater-than symbols, so in this example you need to replace the { with the less-than symbol, and replace the } with the greater-than symbol

ALTER TABLE dbo.{table_name, sysname, table_name}
ADD {field_name, sysname, field_name} {data_type, nvarchar(128), data_type} {nullable, nvarchar, NOT NULL}
CONSTRAINT DF__{table_name, sysname, table_name}__{field_name, sysname, field_name}
DEFAULT ({default_value, nvarchar, default_value})
GO

Within each set of brackets, the 3 arguments are:

• title of the parameter that is displayed in the popup screen
• the data type of the parameter that is to be inserted
• the default value of the parameter that is to be inserted

I have posted a few templates as Word documents with the full code, screen shots & more examples, you can view them here:

column_add_with_default

column_drop_add_with_default

table_drop_create