What are the challenges you face when working across database platforms? Take the survey
Options

New feature request - header comments of SPROCs only

jaffabjaffab Posts: 8
edited July 6, 2012 12:37PM in SQL Doc Previous Versions
Hi,

A new feature request for the SQL DOC module....

When it comes to documenting the Stored Procedures, it would be so much better if there was an option to document the initial comment lines - that is, all commented lines (no matter if they are "--" or within "/*" that appear before the "CREATE PROCEDURE" line.

This way you could create decent documentation for the SPROCs including the author, release version, description of what the SPROC does, etc without all the actual SPROC code (which basically makes the documentation over complex).

Any chance of this?

Comments

  • Options
    Hi (again),

    i have a script which does a rough version of what I am talking about - I can get just the comments - it woul just be nice if the header comments were in the same document as the SQL DOC generated stuff - parameters, called by, calls, etc.

    The SPROC to display just the headers is as follows:



    -- Drop temporary tables if they exist
    IF OBJECT_ID('tempdb..#temp') IS NOT NULL
    BEGIN
    DROP TABLE #temp;
    END

    DECLARE @proc VARCHAR(100),
    @schema VARCHAR(100),
    @proc_id INTEGER,
    @schema_proc VARCHAR(200);

    -- Cursor to work through our procs
    DECLARE procCursor CURSOR LOCAL FAST_FORWARD FOR SELECT p.[name] AS [proc],
    s.[name] AS [schema]
    FROM sys.procedures p
    INNER JOIN sys.schemas s
    ON s.schema_id = p.schema_id;

    OPEN procCursor;
    FETCH NEXT FROM procCursor INTO @proc,
    @schema;

    @FETCH_STATUS = 0)
    BEGIN

    SET @proc_id = SCOPE_IDENTITY();

    -- Create a temp table to hold comments
    CREATE TABLE #temp
    (
    [Text] VARCHAR(4000) NULL
    );

    -- Build schema + proc string
    SET @schema_proc = @schema + '.' + @proc;

    -- sp_helptext to get proc definition
    -- and insert into a temp table
    INSERT INTO #temp
    EXEC sys.sp_helptext @schema_proc;

    -- Just an id we'll use later to identify rows
    ALTER TABLE #temp ADD Id INTEGER NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED;

    -- Display Proc Name
    select @proc as [text],'0' as Id
    union
    SELECT replace(replace(replace([text],'*',' '),'-',' '),'/',' '),Id FROM #temp where Id < (select Id from #temp where [Text] like 'CREATE PROCEDURE%') and LTRIM(RTRIM(replace(replace(replace([Text],char(9),''),char(10),''),char(13),''))) <> '';

    -- Drop the temp table
    DROP TABLE #temp;

    -- Get the next row
    FETCH NEXT FROM procCursor INTO @proc,
    @schema;

    END

    -- Clean up
    CLOSE procCursor;
    DEALLOCATE procCursor;
  • Options
    Thank you for your suggestion.
    I have created a new feature request in our bug tracking software, reference SDOC-1475, for our development team to consider.
  • Options
    GaryJFGaryJF Posts: 6 Bronze 2
    Hi,

    I've been using SQL Doc since version 1 and its a great product but it was missing a few things for me... so I created a "SQL Doc Cleaner" app that you can run right after creating the docs and it will do the following (taken from the about dialog):

    This utility allows the documentation, created by Red-Gate's SQL Doc application, to be distributed to personnel who should not have access to the SQL script.

    The original HTML files are converted to remove script bodies, yet leave the header details, including comments and CREATE statement fragments, intact. Also, any tabs found in the header comments are properly formatted for display. Spaces in both the header and extended properties are correctly converted. A database version number may be specified for inclusion in the main page along with the renaming of the "Extended Properties" title. Finally, any selected supporting documentation, in Excel, PDF or Word format, is automatically hyperlinked in the main page.


    I built the app to my own requirements, of course, but it works very well for me. On the specifc topic that you requested, it leaves the comments, properly formatted (so tabs align etc.), along with the CREATE statement header (including the parameters) so that application developers can see the interface. However, all the following code is replaced with a single *** Hidden Script Fragment *** text line. This occurs for all scripted objects such as procs, triggers and views.

    If you think this is of use to you, let me know and I'll give you a copy.

    Regards,
    Gary
    The client doesn't know what he wants, until he doesn't get it!
  • Options
    I second this request, since we already have a substantial amount of time invested in documentation before the stored procedure code.

    Either this, or something similar using Extended Properties to record the author, creation date and ongoing revision notes. That would be pretty awesome.
Sign In or Register to comment.