Most of the Developers / DBAs I have worked with are maintaining their utility scripts in file system. When ever a script is needed they browse through that folder via SQL Server Management Studio (SSMS) and open it.
I have personally found this method little tedious and not so productive way of doing things. I always prefer these two methods instead:
- Creating an Utility database in the server and having all the required scripts in it
- The other way is to organize our utility script with the help of SSMS Template explorer
Maintaining Utility DB method is self-explanatory and so in this post we would concentrate on the way to make use of Template Explorer for organizing our scripts. Let's get started.
To open template explorer from SSMS follow either of the methods:Option 1: Click View >> Template ExplorerOption 2: Press Control + ALT + T
We would see how to utilize template explorer to organize our utility scripts and how it helps us in improving our productivity.
Creating Custom Folder
Step 1: Right click on "SQL Server Templates" and choose "New" >> "Folder"
Organizing Utility Scripts within the folder:
Step 1: Right click on the new folder we created and choose "New" >> "Template"
Step 2: Lets create a template and name it as "Identify_Remove_DuplicateRecords"
Step 3: Right click on the new template created and choose "Edit"
Step 4: It would open a new blank query window. Paste/Write the required TSQL script and Save it.
Step 5: Repeat Step 1 to 4 until you have all your required frequently used Utility scripts added into this folder.
How to use it?
Now that we have added ALL our utility scripts within a code bank folder. Using it when required becomes very easy.
Step 1: Open a new query window
Step 2: Click on the utility script which you need from the Template explorer and "drag" it into the query window. That's it :)
I would like to add that this method is in no way a replacement to maintaining the scripts in source control. Having it added in the template explorer also as mentioned above would actually just help us improve the productivity.
If you are wondering where these scripts are getting stored. Read on.
I used SQL Server 2005 to create the folder/templates and they are available at \Microsoft SQL Server\90\Tools\Shell\Templates\Sql