Red Gate forums :: View topic - New feature request - header comments of SPROCs only
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Doc 2
SQL Doc 2 forum

New feature request - header comments of SPROCs only

Search in SQL Doc 2 forum
This forum is locked: you cannot post, reply to, or edit topics.   This topic is locked: you cannot edit posts or make replies.
Jump to:  
Author Message
jaffab



Joined: 12 Jun 2009
Posts: 5

PostPosted: Wed Jan 25, 2012 4:38 pm    Post subject: New feature request - header comments of SPROCs only Reply with quote

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?
Back to top
View user's profile Send private message
jaffab



Joined: 12 Jun 2009
Posts: 5

PostPosted: Wed Jan 25, 2012 5:11 pm    Post subject: Reply with quote

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;

WHILE (@@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;
Back to top
View user's profile Send private message
chriskelly



Joined: 19 Apr 2010
Posts: 329
Location: Cambridge, UK

PostPosted: Thu Jan 26, 2012 8:42 pm    Post subject: Reply with quote

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.
_________________
Chris Kelly
Technical Support Engineer
Back to top
View user's profile Send private message
GaryJF



Joined: 17 May 2007
Posts: 5
Location: N. Ireland

PostPosted: Fri Jul 06, 2012 7:34 am    Post subject: I have created a SQL Cleaner app to do just that (and more) Reply with quote

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!
Back to top
View user's profile Send private message
Arcanas



Joined: 07 Feb 2011
Posts: 3

PostPosted: Fri Jul 06, 2012 5:37 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
This topic is locked: you cannot edit posts or make replies. All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group