Anything to document stored procs?
-
It's fairly straightforward to put something together to document tables, but stored procs are a totally different beast. I'm looking for either an application or set of TSQL to produce something similar to the following:
spCustomerUPD_ByCustID
Inputs: @CustID Int
@CustName Varchar(50)
(etc.)
Actions: Update tbCustomer
Insert tbLog
Any ideas of a tool that will do this?
-
There might be a tool already existing, but a start might be to adopt a good commenting style within your procedures when they are created.
The text of the procedure you'll get from sp_helptext. The parameters from INFORMATION_SCHEMA.PARAMETERS and the rest sounds like a laborious task.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Well, we're talking about more than 100 databases, with thousands of stored procedures built over the last 5 years by 15 or so developers, so the structure of them varies greatly.
I can get to the text of the proc, but I don't necessarily want all of that, I just want the primary actions of the procs (in order). Even reading them and doing string searches proves cumbersome when you start getting into the fact that an update can be "update <table>" or "Update <alias>" where the table name may be much further down the line.
You'd think there'd be a tool out there to do this, but...maybe there isn't.
-
Sounds like you should ask your little army of programmers to comment their code... might take them a few days/weeks, but at least it'll be done.
-
Well, we're talking about more than 100 databases, with thousands of stored procedures built over the last 5 years by 15 or so developers, so the structure of them varies greatly.
Sorry, to hear that!
Might be the time now to implement some coding standards. ...and to kick some a**
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Heh, if only it were that easy. Since they're book (and I mean tightly) until at "least" the end of the year, that just isn't happening.
Unfortunately, time to market is more important than quality of code here (and many other places).
Now don't get me wrong...that doesn't negate the complaints when there are problems...
-
Ya that could help too
. Can I come help?? -
So I guess it's reall time to start implementing some coding standards so that at least the new apps are documented correctly.
-
We just brought on a couple of contractual workers to help with the current workload, so if you were local (Central IL) there's a strong chance you could have helped.
-
All I need is a ride (first class ticket from canada
) and I'm on my way.Did you know I'm a black belt?? (well I got one in my closet), so I could really help in kicking butts.
-
Excellent!
-
So you need my adress to ship the tickets (I'd need one for my cat too)?
-
Got two cats already, that's part of the deal, the new guy gets two cats.
-
I don't mind... but it's gonna cost you 4 tickets to send me back.. probabely not best the investement you could make
.So in what part of Europe is Illinois?
-
Bill - until you find the perfect documenting tool...you could start by looping through all the procedures in each of your databases with a
"SELECT name FROM sysobjects where xtype = 'p' " and inserting the results in a table along with an sp_help for each of the procedures that'll at least list the parameters for you! It's a start....
Though you maybe better off investing in some Remi time...a combination of black belt and awesome T-SQL maybe worth the money spent on ferrying cats across the border <;-)
btw - here's a link to what was posted couple of years ago on same topic...
http://qa.sqlservercentral.com/columnists/rmarda/documentingstoredprocedures.asp
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 15 posts - 1 through 15 (of 47 total)
You must be logged in to reply to this topic. Login to reply