Custom Templates for SQL Database Files

July 16, 2016

Templates in SSMS work really well; if you select to create a new stored procedure, you’ll get a skeleton procedure, and then placeholders, which you can populate by pressing Ctrl-Shift-M

SQLTemplate1

But how can we replicate this same behaviour in a VS DB project?

Out of the box, creating a stored procedure will give you this:

SQLTemplate2

The key to changing the above template is to create your own template. The templates in VS are stored wherever you tell them to be:

SQLTemplate3

In my case:

C:\Users\Paul\Documents\Visual Studio 14\Templates\ItemTemplates

Export Template

So, how to create a custom template for, say, a stored procedure? First, create your template. Typically, this will be from an existing VS template:

SQLTemplate4

Now, make your changes:

SQLTemplate5

And then, export the file:

File -> Export Template

SQLTemplate6

You’ll then be asked which item in your project to export:

SQLTemplate7

It treats DB templates like any other, so the next question relates to dependencies. Finally, it asks what you would like to call your template:

SQLTemplate8

If you select to automatically import into VS, then after restarting VS, you should see your new template:

SQLTemplate9

Using Common Templates with Source Control

You obviously can mail this around to the team, and they can all import it. However, another option is that you can source control it.

If you have a look at the directory above, you’ll see that it has created a file there:

SQLTemplate10

Like any other file, you can simply add this to source control. If you do, then you can change the template directories described above to point at the source control folder. That way, everyone on the team can point to up to date source controlled templates.

Notes

Everything in this article applies equally well to other files - it’s just that it is very well documented for this, but not so well for SQL DB projects.



Profile picture

A blog about one man's journey through code… and some pictures of the Peak District
Twitter

© Paul Michaels 2024