Tool or T-SQL method to generate/insert code comment sections

Question

I am looking for a way to insert code comment blocks semi-automatically in all the views, stored procedures, and UDFs in a database (basically every code-based module). I'm fine with generating the ALTER statements and reviewing them myself before running the scripts, I was just looking for a reliable way to generate them. I am not looking for a method to alter the SSMS template, which will not help me alter the existing SQL modules

Obviously SPs contain AS or AS BEGIN, but views only contain AS and I need to ensure that it takes account of varying whitespace.

Answer 1

maybe this tool will be helpful http://www.softtreetech.com/isql.htm

Answer 2

SSMS tools pack (free) has "SQL Snippets" which might be useful, but to add things retrospectively and automatically could be a pain.

BTW, stored procs do not have to have to BEGIN/END

From this BOL link, it shows they are optional (edit: as Charles mentioned I see now)

...
<sql_statement> ::= 
{ [ BEGIN ] statements [ END ] }
...

Answer 3

I would use SQL Compare (from Red Gate) to dump the whole schema to a series of .SQL files. You can then easily add some text to the head of the files before re-running SQL Compare to commit them to the database.

Answer 4

If you have access to Visual Studio Team System Database Edition, you should be able to work through the process described by Joel (import the schema from your database into a SQL Server 2XXX project), then tailor the Code templates provided by Visual Studio. Visual Studio help contains information on customizing these templates. See the topic: "How to: Locate and Organize Project and Item Templates" for more information.

HTH

-- Ozziemedes

Answer 5

Unfortunately, I had to handle this manually - I code-generated the comments and pasted them into each SP, VIEW, TRIGGER and FUNCTION...