I love learning new things. I feel like a kid again at times, my eyes get wide as I imagine new possibilities using whatever things it is I have found. This is how I felt last week when I came across a feature of SQL Server Management Studio (SSMS) that I had not seen before despite it having existed since SQL 2005.
Yeah, that’s right, since 2005. Ten years. Ten long years of not knowing.
I’m not ashamed to admit that I don’t know everything. And I bet there’s someone reading this post (if not today, then someday they will) who never knew about this feature either.
The feature is embedded inside of the templates feature in SSMS. So let’s start there, as I know this is also a little-used feature. You can access the templates with SQL Server Management Studio from the main menu, under View, like so:
Selecting “Template Explorer” will open up the Template Browser on the right-hand side of SSMS and reveal dozens of available templates for you to use:
I’m going to select “Extended property” because who doesn’t love metadata, amirite? I will select “Add Extended Properties to Table” as you see here:
Doing so will open up the template in a new query window inside of SSMS. In this example what you should see is this:
This is where my day suddenly became full of awesome. I found out that I can specify the values for all of the parameters in the script and have them replaced automatically! Go to the Query menu and you will see this option:
That will launch this dialogue box:
And here is where I saw real value. I don’t need to supply literal values here. I could put in variables instead. That means I could use these templates as a way to generate scripts to use for Powershell or SQLCMD (for those of us that still like it old-school).
And this is what the finished product looks like:
Sometimes, it’s the simple things in life, you know what I mean?
Enjoy!
Thanks for sharing! How did I not know about this? Microsoft should be demo’ing things like this at the PASS Summit instead of Excel!
Bill,
Hmmm…maybe find a way to build your own custom template that uses Powershell to invoke Excel that pulls data from a table in a database on a mirrored instance the returns connection string details for your Azure subscription? That would be borderline insanity.
Bill, we have been. For, I don’t know, 5 years? 🙂 http://blogs.msdn.com/b/buckwoody/archive/2010/02/16/code-that-writes-code-a-good-idea-or-not.aspx
That’s useful. Thanks
Welcome!
I’ve apparently done a lousy job of evangelizing templates. Love em even if Aaron Bertrand teases me for talking about them so much!
Yeah, somehow I think I knew about them, forgot about them, found them again, forgot them again, and then FELL MADLY IN LOVE WITH THEM.
This is great functionality indeed, I think its lack of use is, somewhat based on people’s obsession with using GUI for everything. Every time I am teaching a workshop, I make it a point to show this, and the new snippets functionality in SQL 2012. Everyone loves it, but I don’t really think most even remember or use it when they back to their office.
Yeah, I’m not certain why it would be the case more users aren’t taking advantage of the templates. If I had to guess, I would say it is because most of the time we aren’t writing code from scratch. We are either being asked to troubleshoot existing code, or we are finding pieces of code online and doing a cut/paste.
In my experience it is the latter. Most DBA I interact with do not want to troubleshoot code, as they are most operational DBAs or they are supporting 3rd party applications. Where they don’t have much say on code.
Yes, and I think the templates are there to help those folks that perhaps use SSMS as their dev tool.