Sunday, May 10, 2009

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
CONSTRAINT DF__Company__Flag

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:

ADD ccc ddd NOT NULL

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})

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:




No comments: