Modifying Default Template For Stored Procedures in Visual Studio 2015 SSDT

I have always found it very useful to create database coding standards and making it very easy for myself and others to apply those standards is critical, one of those standards is applying a default comment header block to SQL Server Stored Procedures.

In previous versions of Visual Studio I found it very handy to create a procedure template for new stored procedure creation and distribute that temple to my team members for easy implementation.  In Visual Studio SSDT (SQL Server Data Tools) when adding a new stored procedure to a database project, Visual Studio derives a default stored procedure from a template file.

Here is a sample of what I like to see each time a procedure is created

The scaffolding code is generated from a template located here

C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\Items

NOTE: This path is very specific to Visual Studio 2015 SSDT.

The files Create Procedure.sql and Create Procedure.vstemplate are what do the magic.  Make sure you make a backup of the Create Procedure.sql file for safe keeping.

Edit the Create Procedure.sql file and include any comment text or other common items you find yourself typing each time you create a procedure.  For example BEGIN and END statements, setting NOCOUNT ON; NOCOUNT OFF;, etc.

Because of permission issues save this modified file to a DIFFERENT folder

Now delete the original file from the Visual Studio path

Copy the file modified file back into the directory

Restart Visual Studio

Now each time you create a new stored procedure your boilerplate code will be waiting for you.