SSMS Template Replacement

Shortcuts & tips from an Impatient DBA

This series of short posts will demonstrate some lesser-known features, keyboard shortcuts, and other tips that make my day as a DBA more productive. None of these are groundbreaking super-secret features–they are the little things that I do as part of my daily work that make me more efficient.

CTRL + Shift + M

SSMS Template Replacement

One under-used feature of Management Studio is the template replacement feature. SSMS comes with a library of templates, but you can also make your own templates for reusable scripts.

In your saved .sql script, just use the magic incantation to denote the parameters for replacement. The format is simple: <label, datatype, default value>

Then, when you open the .sql script, you hit CTRL + Shift + M, and SSMS will give you a pop-up to enter your replacement values:

SSMS-Template-Replacement-01

Notice that in my contrived example, I still include the single quotes around the template varchar value. Also notice that SSMS syntax highlighting shows the text between the single quotes as teal, not red–SSMS is smart enough to know it’s not regular text.

 

Real-world example

In the above example, there’s not much value-add by using the template replacement. It’s probably easier to just use @variables and highlight-replace.

The template replacement really shines when you have examples where you’d otherwise need to use dynamic SQL. If you have object names or database names that need replacement, this is a great answer. If you work in a multi-tenant hosting environment, and a client name is part of the DB name, this can make your life a lot easier.

SSMS-Template-Replacement-02

 

 

1 Trackback / Pingback

  1. Template Replacement – Curated SQL

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.