SQL Server SSMS Templates – Get A Jump Start On Queries/Code

You are a developer or a DBA. You run queries – a lot of them. This blog post shows you how you can simplify reuse of your queries and use already pre-built ones for which you just have to pass in the specific parameter values.

Frequently run queries (without SSMS templates):

You run some queries more often than others. In fact, if you are like me, you have a library of queries. When you need them, you need them right away. Don’t you hate yourself when you have to search for your favorite misplaced query? Frantic searches put you in even more of an awkward spotlight if your boss is standing behind your back or if you are doing a presentation to a group.

Let us say that my often used query is this query:

IF EXISTS (
  SELECT *
      FROM sys.tables
      JOIN sys.schemas
        ON sys.tables.schema_id = sys.schemas.schema_id
   WHERE sys.schemas.name = N'dbo'
      AND sys.tables.name = N'sample_table'
)
  DROP TABLE dbo.sample_table
GO

After I locate it and before I can use it, I have to remember to substitute the three highlighted values for my use case now. If I forget to substitute one of them I can end-up dropping the wrong table!

Define your own query Template & Parameters (in SSMS templates):

Fortunately, there is a better way to do this – have SQL Server prompt you for the values. To do so, I would have to transform my above query to look like this:

--======================
-- Drop Table template
--======================
IF EXISTS (
  SELECT *
      FROM sys.tables
      JOIN sys.schemas
        ON sys.tables.schema_id = sys.schemas.schema_id
   WHERE sys.schemas.name = N'<schema_name, sysname, your_schema_name>'
      AND sys.tables.name = N'<table_name, sysname, your_table_name>'
)
  DROP TABLE <schema_name, sysname, your_schema_name>.<table_name, sysname, your_table_name>
GO

Now, I can either press CTRL + SHIFT + M (or) choose “Query -> Specify Values for Template Parameters” and SSMS would prompt me for the values in a nice input dialog.

Did you notice that although the schema_name and table_name inputs are defined twice in the query, SSMS realized it and prompted only once? As soon a s you enter the values you want and hit ENTER, the query will be substituted with the right values and you just need to run it by pressing CTRL+E.

Essentially, what we did above was we “templated”, the table drop so that it would work for any table by parameterizing the “varying” parts of the SQL. The template parameters have a specific syntax

N'<variable_name, data_type, default_value>'

In this example

N'<schema_name, sysname, your_schema_name>'

• schema_name is the name of the parameter
• sysname is the data type (not-enforced) – optional
• schema_name is the default value – optional

Are there existing templates built in?

SSMS has a big template library that a lot of users do not realize. To view, press CTRL+ALT+T or  “View -> Template Explorer”. In fact, the “Drop Table Template” above is from this SSMS template library. You can even add your own query templates to the library.

If you need more templates you can search for them online or try some of “Larry Leonard’s” templates from here.

Have coding standards in your company?

If you have coding standards in your company with standard header information and a certain format for procedures, you could quickly build a sample procedure/function/view/trigger or whatever else that people could start using by replacing key aspects. It eliminates some of the hiccups associated with the blank page syndrome and gives even novice users a jump start on coding.

Caution:

If you do start creating your own templates, you are responsible for backing them up. To locate the folder where they are stored

1. Open DOS command prompt
2. Run “echo %APPDATA%”
3. Note the base path
4. Navigate to %AppData%\Microsoft\Microsoft SQL Server\{SQL Server Version}\Tools\Shell\Templates\Sql\

(where %AppData% is the base path from [c]

and {SQL Server Version} = 90 for SQL 2005, 100 for SQL 2008, 110 for SQL 2012, 120 for SQL 2014 and 130 for SQL 2016)

All database query tools and IDE’s, SQL Server or otherwise will offer this basic ability to create query templates. You just have to look for it to exploit this feature. Always remember – backing up your templates is not the tool’s responsibility.

References:

http://msdn.microsoft.com/en-us/library/ms189396(SQL.105).aspx – Specify template parameters
http://msdn.microsoft.com/en-us/library/ms179334(SQL.105).aspx – Use templates in SSMS
http://msdn.microsoft.com/en-us/library/ms166841(SQL.105).aspx – Create custom templates
http://sqlsoundings.blogspot.com/2010/03/using-ssms-templates-for-fun-and-profit.html – SSMS templates blog
http://sqlsoundings.blogspot.com/p/sql-server-templates.html – Larry Leonard’s templates