SSMS Tips: Templates and Control+Shift+M

Templates-Code-RecyclingDo you use templates to save typing in SQL Server Management Studio?

Templates are extremely handy for tasks that you need to do repeatedly, but with different parameter values. The coolest part is that once you get the hang of them, you can create your own custom templates.

Whether or not you’re using a template, always make sure that you’re connected to the right server before you hit execute. Don’t ask me how I know to mention that reminder 😉

Here’s a demo of how to use a simple template. Use the keyboard shortcut Control+Shift+M to open the template parameter editor.

The mnemonic I use to remember the shortcut is “Control and Shift Microsoft!” (Does that make me sound like a power hungry person? Hmmm.)

Template-Demo

Previous Post
Altering an INT Column to a BIGINT (Dear SQL DBA Episode 11)
Next Post
Teach Yourself SQL Server Performance Tuning (Dear SQL DBA Episode 12)

Related Posts

13 Comments. Leave new

I use templates every day for larger queries, and as an adjunct to a snippet manager.

Here’s one I find useful for searching through a large number of stored procedures:

DECLARE @searchText VARCHAR(100) = ‘%searchtext%’
SELECT Distinct DB_NAME() AS DBName, SO.Name AS SPName
FROM sys.sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
AND SO.Type = ‘P’
AND SC.Text LIKE @searchText ESCAPE ‘|’
ORDER BY DBName,SPName

Reply

What version of SSMS are you using ?

Reply
Brendan Mason
August 9, 2016 1:06 pm

To help with the wrong server scenario, you can wrap your template code inside an IF block and supply the target server name as a parameter:

IF (@@SERVERNAME = ”)
BEGIN
— Do template stuff…
END
ELSE PRINT ‘Wrong server, dummy!’

Reply
Brendan Mason
August 9, 2016 1:08 pm

That was supposed to read IF (@@SERVERNAME = ‘<server_name, sysname, server_name>’)

Reply

Thank you very much! This will save me loads of time.

Reply

Can I add my own template so it can be used as a snipping tool?

Reply

    You can add your own template with parameters– there’s a link at the top of the post with instructions on how to do that.

    I’m not sure what you mean by “snipping tool”, though?

    Reply

I find it annoying that there are so many different ways to do this type of thing and that they don’t work across both SSMS and SSDT. If they did i’d use them more. instead I just use Notepad++ with the FingerText plugin to make quicker and simpler templates/snippets and copy that into ssms to execute

Reply

Not sure if they do yet; I don’t have it installed.

Reply

Leave a Reply

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

Menu