A few posts back I mentioned that I used the “Templates” feature in SQL Server Management Studio to track all of my script librarires. I wanted to expand on that a little bit, and make a correction.
Like many DBA’s, I have a series of scripts I have collected over the years. I created some as I learned a new feature, and I got several others from magazines, friends and web sites. (I try to attribute ownership whenever I can) I used to have a file location I stored these scripts on, and then as time went by I stored them on a USB stick.
Inside SSMS there is a feature called “Templates”. I’m told not a lot of people don’t use these, but I sure do. I copy the ones provided by Microsoft to another location and replace them with my script library. I then use a program called “SyncToy” and replicate the scripts up to a share, where other people can sync them back down to the local machine.
If you’ve tried this, you may notice on Vista that after you change the files, some keep coming back. The reason is that a certain location is checked by SSMS:
C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\sqlworkbenchprojectitems\Sql
And then these files are copied here (the location is different on XP):
C:\Users\yourloginname\appdata\Roaming\Microsoft\Microsoft SQL Server\90\Tools\Shell\Templates\Sql
So if you make changes, they are stored in the latter directory, and if you want to permanently change things, you’ll want to edit the former. Remember, during patches and so on the first location might be re-written with the Microsoft-supplied stuff.