New feature request - header comments of SPROCs only

jaffab

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?

jaffab

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;

chriskelly

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.

GaryJF

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

Arcanas

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.