How can I automatically prepend comment blocks to SQL Server stored procedures?

Question

In my organization, the standard is to comment all stored procedures with a comment block that looks like this:

/*-- =============================================
-- Created by: Chris McCall
-- Created date: 08.05.2009
-- Purpose: Inserts new setting value, code and description
-- Modifications:
-- <Date> <Programmer> <Change>
-- =============================================*/

I don't find this to be particularly useful, since the stored procedure is named usp_utl_CustomSettingsInsert anyway, and the comments are not guaranteed to be accurate. I usually ignore these blocks unless I have a problem and need to contact the original developer (who has long since departed, cackling maniacally, in a helicopter).

However, it's not up to me, so I have to do this. Is there any way, with a trigger or some other SQL Server magic, to create these comment blocks with nothing more than the power of my mind?

Answer 1

create a template in your editor

EDIT

If you want to alter the text of a procedure you can look at syscomments:

select text from syscomments where id=object_id('YourProcedureName') order by colid

If you put in long dummy tags like "<<:REPLACE XYZ:>>" in the source of the procedure, you could use: (I WOULD NEVER DO THIS AND DO NOT RECOMMEND THAT ANYONE ACTUALLY TRY THIS!!!)

UPDATE syscomments
set text=REPLACE(
                    REPLACE(text,'"<<:REPLACE NAME:>>','new name')
                    ,'"<<:REPLACE DATE:>>',GETDATE()
                )
where id=object_id('YourProcedureName')

Answer 2

If you're using SQL Server 2005... Check out Template Explorer....you can find it in SQL Server Management Studio under View...then Template Explorer

Once in Template Explorer...go to the Stored Procedure section and take a look at the templates there.