Snippets for SQL Server Management Studio (SSMS)

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)

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)

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)

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

About these ads

14 Responses to “Snippets for SQL Server Management Studio (SSMS)”


  1. 1 Raja March 1, 2010 at 4:34 pm

    This is awesome. I never knew such a thing exists.

    Thanks for the post.

    Regards,
    Raja

  2. 2 Raj March 30, 2010 at 4:47 am

    This is simply superb. Thanks for sharing the information.

  3. 3 Petr Parik July 19, 2010 at 8:42 am

    IMHO, code snippets simmilar to visual studio would be much better

  4. 6 Stefan Milne November 1, 2010 at 10:09 am

    Excellent!!! Really handy

  5. 7 msk January 19, 2011 at 1:53 pm

    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.

  6. 8 Attila Papp April 12, 2011 at 10:23 am

    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.

  7. 9 Ahad Rasouli April 12, 2011 at 11:13 am

    Another great SQL functionality, thanks for the demonstration.

  8. 10 Roger Osborne August 17, 2011 at 11:49 am

    I never knew about this, thanks!

  9. 11 srinivasu dandamudi September 22, 2011 at 1:52 pm

    Thanks , this is very helpful to me. great..

  10. 12 Tom December 1, 2011 at 6:10 am

    Thanks. Just what I was looking for

  11. 13 dinesh August 11, 2012 at 8:19 am

    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”..

  12. 14 Paul Sheppard May 3, 2013 at 10:24 am

    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]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s





Follow

Get every new post delivered to your Inbox.

%d bloggers like this: