Automate comments in stored procedure? (e.g., author, modified date...)

Question

Post reply

Automate comments in stored procedure? (e.g., author, modified date...)

peterzeke

SSCrazy Eights

Points: 9003
More actions
January 16, 2009 at 1:08 pm

#125596

A couple of years ago I came across a technique or script that ensured that basic info about the person who created a stored procedure was entered into the top of a stored procedure within a comment block.
For the life of me, I can't find this article/tip. I also haven't used it in a long time, but I do recall having tried it out when I first read about it. It worked quite well. Not sure why I didn't keep it around, though.
Does anybody know what I'm talking about or perhaps someone has a suggestion on how to record the name of the author, creation date, modfiied date into a comments of a stored procedure? I'm guessing it has something to do with modifiying a sql template.
Although we use Visual Source Safe, there are times when either myself or a colleague whips up a chunk of code that never makes it to VSS. Coming across some of these stored procs & UDFs from time to time can be frustrating if the code looks a bit wacked, but you don't know who or when the script was written.
Thanks in advance,
Pete

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

twillcomp SSC Eights! Points: 989 More actions · Answer 1

Pete,

I can't recall the article but, have you considered a DDL trigger?

Have a look at ddlDatabaseTriggerLog in AdventureWorks sample (under Programability -> Database Triggers).

If you don't want to create a database table to record these changes, I was thinking you could add an extended property to the stored procedure maybe?

I've only had a brief look at the databaselog table to see what events and data items are recorded and it would seem there could be some useful examples there.

cheers

Tony

gyessql Ten Centuries Points: 1097 More actions · Answer 2

I'm not very clear on this. As I know, VSS and sql code is related via macros. In VSS the macros are defined.

For example: $ID, to say when the sql file is checked in VSS, wherever $ID is found in sql code, replace it with something (that is defined in VSS like name, modtime etc)

peterzeke SSCrazy Eights Points: 9003 More actions · Answer 3

Thanks for the feedback -- After doing some more searching around, I've determined that what I've been searching for is likely a template. I found a couple of articles here on sqlservercentral about the handiness of templates, and one of the articles seems familiar to me (perhaps it's the article I first came across a couple of years ago.)

Although I thought I had once come across something that helped add comments automatically when a stored procedure is altered, I've come to conclude that I'm simply mixed-up-in-the-head on this one.

So, I'll end my quest and toy with templates a bit for the time being.

--pete

gyessql Ten Centuries Points: 1097 More actions · Answer 4

peterzeke (1/22/2009)
Although I thought I had once come across something that helped add comments automatically when a stored procedure is altered, I've come to conclude that I'm simply mixed-up-in-the-head on this one.

Ya, its kind of a template that adds comments whenever u check-in. You are right, whenever the stored proc is modified and checked-in i.e., whenever the version of the file is changed, the comments are automatically added, and the template helps in this but don't know how. I use CVS and it updates the comments (author, version and modtime) everytime.

If you come across the article, please post it. It would be helpful.