For years I have used the template browser in SQL Server Management Studio to store commonly used queries. I have created a sub-folder prefixed with 'aa_ ' so it is listed first then created more sub-folders under that to store my various queries. Then i have those queries available to every server I am connected to. This keeps me from having to find remember if I stored the latest version in Confluence, on my laptop or on a network share. They are always available right from SSMS.
if you haven't used SSMS templates before, to to View→ Template Explorer to bring up the browser. Then you can create a folder structure there.
Now if you are like me, you have more than one edition of SSMS installed on your laptop. Currently I have SSMS 2014, 2016 and 2017. The different versions of SSMS use different folders to store templates, so with each new version I have to copy my aa_Rutherford folder from the old template location to the new. The problem is now they are out of sync, if I add folders or files in one version of SSMS they don't get updated in the others.
I solved this problem using something called symbolic links. If you have used Linux before you are probably familiar with these as they are used quite a bit by the operating system. Basically a symbolic link is a fake file system object that points to a different and real file system object. You can create a folder in one directory that is a link to a folder somewhere else on your machine. Then when you browse to that folder, you see the files and folders that exist in the other directory.
Might be a bit confusing so lets see an example. The command you use is: mklink /D [symlink_name] [C:\Path\To\Actual_folder]
Suppose we have a folder name aa_Rutherford located at C:\JRTemp and we wanted to create a symlink at C:\JR_NewTemp. First you need to launch a command prompt as administrator.
- Use 'CD' to change to the new directory
- We can use the 'DIR' command to see that it is empty
- Run our 'MKLINK' command to create the symlink
- Run 'DIR' again to see the new symlink and where it is pointing.
- Then run 'DIR' on the symlink and you can see the files that are inside the destination location.
Here are the locations for the template folders in SSMS
2014 - C:\Users\[username]\AppData\Roaming\Microsoft\SQL Server Management Studio\12.0\Templates\Sql\
2016 - C:\Users\[username]\AppData\Roaming\Microsoft\SQL Server Management Studio\13.0\Templates\Sql\
2017 - C:\Users\[username]\AppData\Roaming\Microsoft\SQL Server Management Studio\14.0\Templates\Sql\