Today while working I created a new Stored Procedure using SSMS 2008, through
This returned me a well defined new SP template as usual. Here I was supposed to add some extra lines of comment like modified by, date, purpose etc. This prompted me to think how nice it would have been if I could get these automatically written for me every time I create a new SP. So I googled and straightaway found a stack-overflow link telling about Template Explorer in SSMS. From template explorer you can view all the default SSMS templates and can edit any of them.
I edited template for new stored procedure to add new comment lines as per my requirement and also changed number and type of default parameters.
You can also use it to add some other commonly used lines like TRY/CATCH block :)
Shortcut to open template explorer: CTRL + ALT + T
These templates are stored in your file system as plain SQL files which you can edit without SSMS as well. Location for it is below:
On my XP machine having SQL Server 2005 Express:
C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SsmseeTemplates\Sql
On my windows 7 machine having SQL Server 2008 R2 Developer Edition:
C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql
Database => Programmabilityv=> Procedures => Right Click Select New procedures
This returned me a well defined new SP template as usual. Here I was supposed to add some extra lines of comment like modified by, date, purpose etc. This prompted me to think how nice it would have been if I could get these automatically written for me every time I create a new SP. So I googled and straightaway found a stack-overflow link telling about Template Explorer in SSMS. From template explorer you can view all the default SSMS templates and can edit any of them.
I edited template for new stored procedure to add new comment lines as per my requirement and also changed number and type of default parameters.
You can also use it to add some other commonly used lines like TRY/CATCH block :)
Shortcut to open template explorer: CTRL + ALT + T
These templates are stored in your file system as plain SQL files which you can edit without SSMS as well. Location for it is below:
On my XP machine having SQL Server 2005 Express:
C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SsmseeTemplates\Sql
On my windows 7 machine having SQL Server 2008 R2 Developer Edition:
C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql
