Generating documentation for stored procs

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Generating documentation for stored procs

Author

Topic

nikita
Starting Member

7 Posts

Posted - 2007-12-19 : 02:52:26

Hi,

I have lots of stored procedure and am marking comments on them. I wanted to know free tools that can be used to generate documentation out of those comments.

Is there any particular format in which the comments have to be written

I am using Sql 2005

thanks.

jordanam
Yak Posting Veteran

62 Posts

Posted - 2007-12-19 : 11:29:11

Comments must begin with two hyphens, or they have to be surrounded by /* comment here */

If you are asking more about logical structure of documentation, when you script new procedures from the Management Studio, they start with a block that looks like:

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================

That's a pretty good template to follow. You might want to write in a change history, and use dates where necessary.

You have to know who you are writing comments for, and then you can tailor them accordingly. Why are you documenting the procs? Is it because someone is going to take over the database and they need to see a quick sentence or two about what each proc does? Is it because each one is being actively developed by multiple people? Is it because management requires a comprehensive audit of your database tech?

As for a tool, there is a master view/table (I forget which) you can query that gives you the entire text of a sproc. If you began and ended your comment block with something predictable and not in the sproc (like -- Comment block by Me -- ) then you could write a query that just picks out the comments and name of the sproc.

Maybe someone else can help with that part.

pootle_flump

1064 Posts

Posted - 2007-12-19 : 11:44:54

quote:
Originally posted by jordanam

Maybe someone else can help with that part.

This is a QAD script I use for that sort of stuff:

DECLARE @to_search_for VARCHAR(100)

SELECT	@to_search_for = '%%'

SELECT	name
		, SUBSTRING(all_obj_code, text_location - 20, 20) 
			+ UPPER(SUBSTRING(all_obj_code, text_location, text_len)) 
			+ SUBSTRING(all_obj_code, text_location + text_len, 20) AS text_searched_for
		, type_desc
		, created_date
		, modify_date
		, all_obj_code
FROM	--Relevent code objects
		(SELECT	o.name
				, PATINDEX(@to_search_for, sm.definition) AS text_location
				, LEN(REPLACE(@to_search_for, '%', '')) AS text_len
				, UPPER(SUBSTRING(sm.definition, PATINDEX(@to_search_for, sm.definition), LEN(REPLACE(@to_search_for, '%', '')))) AS text_searched_for
				, sm.definition AS all_obj_code
				, o.type_desc
				, o.create_date AS created_date
				, o.modify_date AS modify_date
		FROM	sys.sql_modules AS sm
		INNER JOIN	
				sys.objects AS o
		ON	o.object_id = sm.object_id
		WHERE	sm.definition LIKE  @to_search_for) AS objs
ORDER BY name

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-20 : 08:35:16

I wrote a tool called sqlspec that parses header comments and puts them in documentation. see the link in my sig.

elsasoft.org

nikita
Starting Member

7 Posts

Posted - 2007-12-20 : 23:16:30

thanks to all of you for the help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources