How many times have you had to open an existing SQL object and copy the header information to add to a new procedure, view or function? How many times have you done this and forgot to change the information in the header? Do you like standards? If you answered “Yes!” to any of these questions then templates are for you! Microsoft SQL Server Management Studio comes with a plethora of built-in templates, but this little gem of a feature also allows you to create your own!
To open template explorer, select View -> Template Explore or press Ctrl+Alt+T. This will open a side bar (by default, but you can change it) with the templates installed by default. To add your own templates, right-click on “SQL Server Templates” and then “New” from the context menu. This will give you the option to create a new folder or template. I prefer to keep my user-created templates in their own folder, since that makes it easier to transfer templates between machines.
Let’s create a folder called “CodeSnippets” as shown in the screen shot below.
Now that we have our folder created, let’s create some templates. We will create a basic header template and a stored procedure template. To create our templates, Right click on the newly create folder and select New -> Template from the context menu.
Now that we have the blank templates created, let’s add some content. Right click on the template and select “Edit” from the context menu.
From here you can write or paste your content into the template.After creating your templates, usage is as simple as dragging and dropping your template into the proper location in the query window.
I mentioned earlier about transferring templates between machines. To move your templates between machines browse to
C:\Documents and Settings\[User Profile]\Application Data\Microsoft\Microsoft SQL Server\[90 for SQL 2005 or 100 for SQL 2008]\Tools\Shell\Templates\Sql\[Your Snippet Folder Name]
for Windows XP and Windows Server 2003 or
C:\Users\[User Profile]\AppData\Roaming\Microsoft\Microsoft SQL Server\[90 for SQL 2005 or 100 for SQL 2008]\Tools\Shell\Templates\Sql\[Your Snippet Folder Name]
for Windows Vista, Windows 7 and Windows Server 2008. (Please replace the items in  with your specific value for your environment) and copy the data to the appropriate path on the source machine.
I hope you find this as useful as I do! Happy coding!