RE: Extracting inline stored procedure comments as Description extended properties

 

 

I came up with this simple template to solve the problem of adding descriptions for stored procedures and their parameters.  As explained in the template header comment:

 

 * Comment: This template was inspired by Jason Alexander's DBDocumenter templates
 *   (http://www.ngallery.org/dbdocumenter/), and provide a simple way to add Description
 *   extended properties for stored procedures and their parameters, by parsing inline SQL
 *   comments.
 * Note: Compiled with CodeSmith 2.5.11 RC2, may not work with earlier versions.
 * Example:
 *   -- Any comment, multilined or single lined, using -- or C style comments,
 *   -- prior to the procedure, will be picked up as the procedure Description
 *   CREATE PROC FooBar
 *      @Foo int, --This will become the description for the @Foo parameter
 *      @Bar varchar(50) -- And this the description for @Bar
 *   AS
 *   UPDATE FooBar
 *   SET Bar = @Bar --An inline comment here will be ignored
 *   WHERE Foo = @Foo
 *
 

 * Modified:
 *  Jan 08 '04 by POA
 *   - Initial version
 *  Jan 09 '04 by Eric J. Smith
 *   - Changed it so that the template does not assume that each parameter will have a comment.
 *   - Changed the "CREATE[\s]*PROC" regex to "CREATE[\s]+PROC" since there must be a space between
 *     CREATE and PROC.
 *   - Made changes to the GetSqlParameterComments method to only pull comments from within the
 *     proc definition instead of the entire proc's code.
 *  Jan 09' 04 by POA
 *   - Added Eric's changes to this change log
 *   - Minor code cleanup

 

 

Example output

 

-Oskar

Post Edited (Oskar Austegard) : 1/9/2004 5:46:44 PM GMT

Oskar
mo.notono.us

Oskar
mo.notono.us

 

Here is a slightly updated version that seems to work on all of our procs.  Here is what I changed:

 

1. Changed it so that the template does not assume that each parameter will have a comment.

2. Changed the "CREATE[\s]*PROC" regex to "CREATE[\s]+PROC" since there must be a space between CREATE and PROC.

3. Made changes to the GetSqlParameterComments method to only pull comments from within the proc definition instead of the entire proc's code.

 

Thanks,

Eric J. Smith

Oskar
mo.notono.us

Oskar
mo.notono.us