Populate SSMS templates with your own code

Posted: April 17, 2012 in Robocopy, SSMS, Templates

When SSMS starts up it copies the files from here:
%ProgramFiles%\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\sqlworkbenchprojectitems\sql
in my case on a 64bit machine
C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\sql

To your user folder (this is an example for me, my login is lynchs)
C:\Users\lynchs\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql

The first thing I did was go into %ProgramFiles%\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\sqlworkbenchprojectitems and copy all the templates out somewhere. I’ve never used them but I guess I might.

You should leave the file SQLFile.sql – this is your default New Query file.
I also left Sql.vsdir although I’m not sure what that one is for/

Then I went into C:\Users\lynchs\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql and deleted all of it.

Now I have no templates. I do have my own TSQL Library and so I set up a robocopy job to populate my templates directory from my library. It looks like this:
robocopy “H:\IT\Delivery\DBA\SQLTools\TSQL” “C:\Users\lynchs\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql” /R:2 /W:2 /Mir *.sql

I choose to do it this way because the templates directory is more volatile. I’ve already mentioned how it tries to copy files from %ProgramFiles%\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\sqlworkbenchprojectitems. I am also more likely to accidently delete the file from templates in SSMS than I am from my own library (which could be set to readonly).

Remember if you want ot update or reorganise the templates you do the work in your TSQL Library and robocopy will then modify the templates folder. I have robocopy set to run on log-on.


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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s