SQL Server 2005 Templates
I have found the SQL Server 2005 templates to be extremely useful. I have created custom templates for creating a new stored procedure, scalar functions, table-value functions and views. The benefit of these is that it creates a standard for creating these SQL objects. In my templates I have also included hooks to source control. You will also notice that in my template I always check if the object exists and if it does not I create a dummy shell for the object. This allows me to always do an alter of the object as opposed to a drop and a create which can be bad if the create fails since then your object would be gone. Below is the template for a stored procedure.
--$Author: $
--$Date: $
--$Modtime: $
--$History: $
if not exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].<proc_name, sysname,sample_proc>') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
exec('create procedure [dbo].[<proc_name, sysname, sample_proc>] as select 1')
GO
Alter PROCEDURE dbo.<proc_name, sysname, sample_proc>
AS
GO
grant execute on dbo.<proc_name, sysname, sample_proc> to public
go
-- Template Created by Gregg Stark http://sqladvice.com/blogs/gstark
To use these templates, extract them to
Window XP: C:\Documents and Settings\<user>\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\Templates\Sql\<some folder>.
Windows Vista: C:\Users\<user>\AppData\Roaming\Microsoft\Microsoft SQL Server\90\Tools\Shell\Templates\Sql\<some folder>
For the folder you might want to use your company name or you can put them in one of the default folders.
Once you have put them there within SQL Server Management Studio go to the View menu -> Template Explorer.
You should then be able to see the new templates in the folder you put them in. To use them, simply double click on one
and then you can go to the Query Menu -> Specify Values for template Parameters (Ctrl - Shift - M).
A couple of gotchas on using these.
1) The templates folder doesn't seem to show until you actually view the template explorer so you may have to do this before you put the files in the right place.
2) Unfortunately there isn't a refresh button on the template explorer. The only way I have found to get it to see the new templates is if you completely close SSMS and re-open it. What a pain!!! I hope this gets fixed in the next release.
The templates are available here. Please let me know of any enhancements you would like to see or any bugs you find and I will update accordingly.