I wrote about the Template Explorer, which comes with the SQL Server tools and is visible in Management Studio (SSMS). It’s handy, but there are limited code items in there. What if I want more?
That’s easy. Suppose I decide that I often need to create procedures with the EXECUTE AS clause. I usually do this:
CREATE PROCEDURE MyProc @id INT WITH EXECUTE AS OWNER AS BEGIN -- do work BEGIN TRY COMMIT END TRY BEGIN CATCH ROLLBACK EXEC uspErrorHandler; END CATCH END
It’s a basic template of stuff I do. Let’s stick this in our Template Explorer.
The first thing I do is go to the Stored Procedure folder. I can right click it and I’ll see this:
I choose template and a new one is created. I enter a name and I have a template. The first 6 templates here are defaults. The last one, highlighted below, is the one I created.
Now I right click it again and select Edit. At this point, it will open in a query window. This is just a file in my file system (under ), and like any other query, I can edit it. I paste in my script from above, and change a few items to parameters.
Now I can save this, and the next time I need this, just drag it into the main window and customize it.
Voice of the DBA 
I had in fact filed a suggestion with MS Connect to allow easy import of templates into the Template Explorer (https://connect.microsoft.com/SQLServer/feedback/details/668497/ability-to-import-templates-deploy-custom-templates-into-the-template-explorer) – the current option is quite messy and error-prone (due to the level of human interaction required) – especially if you want to share the same set of templates across an entire development team.
The Connect item unfortunately was marked as “Will Not Fix” and I ended up thinking that not many people are still using it (after SQL 2012). Happy to note that it is still being used and I am not the only one to try an import a custom template into the template explorer.
LikeLike
Unfortunately, MS has ignored lots of the tooling, and templates along with this.
LikeLiked by 1 person
More recent versions of SSMS and VS allow for the use of snippets, to which you can bind these templates as well to save a few click strokes (although to be honest, I haven’t spent the time to learn Microsoft’s implementation). I have an addon which allows me to bind the code of a template to a keystroke combination. So for instance I can type prc+ and load up all the template code for a brand new procedure.
LikeLike