Anything to document stored procs?
-
Thanks for the link. I've actually began writing something that so far seems like it may do what I want. I'm currently putting it together in a VB application, but may port it over to SQL after it's completed. It's just easier doing a lot of this looping/string manipulation in VB initially.
I'll post back here with my results.
-
First run...
I ran it against a fairly messy stored procedure...multiple comment sections, aliased table names, updates & executes in the same proc, etc. Here's what it extracted:
1 Exec EventTracking.dbo.spProcessLogINS
2 Update <dbname>.dbo.<tablename>
3 Exec EventTracking.dbo.spProcessLogUPD
4 Exec DatabaseMaintenance.dbo.spSendMail
5 Exec EventTracking.dbo.spErrorLogINS
6 Exec EventTracking.dbo.spProcessLogUPD
Note that I replaced real db/table names for #2 above.
It's setup to locate and report any update/insert/delete/exec statements and I'm probably going to at least offer the option of bringing back the affected columns (for updates) and rows (where clause) as well. I'm thinking about not returning all joined tables as we have some procedures that join probably 10 to 15 tables, and that's going to make the documentation less readable which of course negates the reason(s) for it in the first place.
Thoughts/suggestions?
-
General opinions...
"I'm thinking about not returning all joined tables as we have some procedures that join probably 10 to 15 tables, and that's going to make the documentation less readable..."
I like to keep things simple so I would tend to agree with you about this...also depends on how detailed you want your documentation to be...personally, I have a "template comment box" that I use in all my procedures (I'm an obsessive compulsive comments freak)...
/*******************************************************************
Name:
Function:
Inputs:
Declarations:
Outputs:
DateWhoComments
History: 05/30/03sushilacreated initial.
**************************************************************************/
Then again...this is "after the fact" so of little use to you!
I know I'd have tried to do this via query analyzer using sp_depends, sp_help etc; dumping the results in a table and then querying this table for documentation...
Know this is neither here nor there...falls under "thoughts" and not "suggestions"...<;-)
**ASCII stupid question, get a stupid ANSI !!!** -
No, good info. Actually we have those comment sections as well, it's just that when someone from another department is standing over a dev's shoulder and they're having to adjust things in 5 procs on the fly because the design is changing (right then of course) the devs skip what they can which is of course...the comments section.
-
No offense to your developpers, but I think it's the most impotant thing in the proc (besides the fact that it works).
I challenge anyone to tell me what this code is doing without executing it :
if object_id('Test') > 0
DROP TABLE Test
GO
CREATE TABLE [Test] (
[id] [int] NOT NULL ,
[Name] [sysname] NOT NULL ,
[Colid] [smallint] NOT NULL ,
[Colid2] [int] NULL ,
[LastId] [int] NULL ,
CONSTRAINT [PK_Test] UNIQUE CLUSTERED
(
[id],
[Colid],
[Name]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Insert into Test (id, name, Colid) Select id, name, Colid from dbo.SysColumns
--Update Test set Colid2 = null, LastId = null
--Select * from test
Declare @Int as int
set @Int = 0
declare @LastId as int
set @LastId = -1
declare @Reset as bit
set @Reset = 0
update dbo.Test set @Int = Colid2 = case When @LastId id THEN 0 ELSE @Int END + 1, LastId = @LastId, @LastId = id
Select * from test
GO
DROP Table Test
- You might want to check out ApexSQL Doc. It should solve your documentation problem. I believe it documents stored procedures as well as the rest of your database. You can give it a test, with the trial version. http://www.apexsql.com.Good luck
-
Thanks for the link. I just viewed it's sample output at http://www.apexsql.com/zips/apexsqldoc_example.chm
While it seems capable of listing all of the objects, I don't think it shows the flow of calls where one proc calls three others, one of those calls two others, etc.
I've downloaded the trial and am running it now. I'll reply back with my thoughts on it fitting my situation.
-
I just had a look at it and it looks incredible. I assume that there are flaws but I can't find 'em atm :-).
Anyone took up my challenge yet?
-
"Anyone took up my challenge yet?"....
I'm in my QA doing so right now....Oops..didn't mean to let that slip!<;-)
Bill - now all you have to do now is compare the cost of buying ApexSQL vs. importing Remi...
**ASCII stupid question, get a stupid ANSI !!!** -
Apex is probabely cheaper, but I'm funnier and can do more than just documenting
. -
I'm trying to nail down precisely how their dependency level is determined at the moment. That's a vague representation of what I'm looking for, it's just whether or not it's producing what I think it is/should.
-
Ya just relooked at that part, they are using the sp_depends stored proc to get that info... and we all know how reliable that info is.
Still seems like a great tool though.
Anyone up for my challenge yet?
-
"Anyone up for my challenge yet?"...
Remi - you should start a new post with your challenge...you have a limited audience here...
??? what're the cons of sp_depends ???
**ASCII stupid question, get a stupid ANSI !!!** -
The same cons as with most all of the other existing tools I've found, they don't give me that "treeview" of calls allowing me to drilldown from a high level to a detail level.
-
Maybe I will extend the challenge... but Joe Celko's gonna kill me if I post code like that, or have me banished from sql...
Anyways, the problem with sysdepends is that it doesn't always return all the objects that are dependent on a table for example.
Try this :
Create table Test
(
a int identity(1,1)
)
GO
Create Proc dbo.TestA
AS
select * from dbo.Test
Go
exec sp_depends 'Test'
--dbo.TestAstored procedure
--now in enterprise manager, edit the table and remove the identity property of the column, save.
exec sp_depends 'Test'
--nothing is referencing by... (sorry for the lazy traduction)
Since the table was effectively destroyed and recreated by EM, all the dependecies with the stored procs, views, functions... were also lost with it, even if we actually didn't delete the table. That's why we can't trust sp_depends with sql server 2000, maybe this is changed in Yukon??? The order in which you create the objects can also have an effect on this...
But the fact is that if you always script everything out and don't use EM to edit tables, you might not have a big problem with dependencies. So it's always safe to have a little check in the syscomments table to see if an object is actually used somewhere in the system before deleting it (and even that can fail if the text is spread across 2 rows).
Viewing 15 posts - 16 through 30 (of 47 total)
You must be logged in to reply to this topic. Login to reply