Archive for the ‘Templates’ Category

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.