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.
Template Context Menu (Folder)
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.
Template Context Menu (Template)
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.
Template Context Meny (Edit)
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!
~Ron
This is awesome. I never knew such a thing exists.
Thanks for the post.
Regards,
Raja
This is simply superb. Thanks for sharing the information.
IMHO, code snippets simmilar to visual studio would be much better
I agree. There are also snippets through third party tools which I use and prefer over the native functionality.
I agree too. This isn’t snippets. This is templates.
Excellent!!! Really handy
I decided to write an application for store my template. when I was searching about app like this on google, I found your article. I raly dont know this feature of SQL. Thanks man.
I found this too slow. I prefer the way when I can define a code template (snippet), a short string for identify that and then a hotkey indicates a replacement. Eg.
I enter ‘slc’, press hotkey, the ‘slc’ turns into ‘select * from ‘
I enter ‘dcli’, press hotkey, the ‘dcli’ turns into
‘declare @i int
set @i = ‘
etc. I don’t know why the new generation editors forgot this old way of coding boosting.
Thanks anyway.
Another great SQL functionality, thanks for the demonstration.
I never knew about this, thanks!
Thanks , this is very helpful to me. great..
Thanks. Just what I was looking for
I am using sql developer edition 2005..There following option
1)database
2)security
3)server object
4)Replication
5)Management
6)Notification services
Can tell which option select make “New Folder”..
Seems to be a new location for SQL 2012, this is on windows 7 enviroment.
C:\Users\[user profile]\AppData\Roaming\Microsoft\SQL Server Management Studio\11.0\Templates\Sql\[your snippet folder]