June 20, 2006 at 10:24 am
I am trying to reverse engineer a large SQL Server 2000 DB with extensive business logic buried in a couple of hundred, sometimes very large, and very poorly documented stored procedures. I need to pull the Routine_Names and Routine_Definitions for a subset of routines (most frequently run and nightly batch process routines--I know what these are).
Problem is that the Information_Schema.Routines returns Routine_Definition as a nvarchar(4000) field and many of the routines are over 4000 characters. The view is locked down so I need to recreate the view logic definition (or modify the view if anyone knows how).
Also, I saw a discussion on another thread that mentioned the possibility of listing the table dependencies for a procedure. Can anyone point me to how to do this?
Last question, can anyone recommend a good book on mining for metadata.
Thanks in advance.
Larry
June 20, 2006 at 10:32 am
Procedure definitions are in the syscomments table:
Select object_name(id) As Routine_Name, colid As Chunk, text
from syscomments
For large procedures with more than 4000 bytes of text, the procedure definition spans over multiple records, with the colid column providing the sequence.
June 20, 2006 at 11:14 am
Thanks, this was very helpful. Expanded your example a bit to give me the column outputs I want:
select top 10 sysobjects.id AS [ID], sysobjects.name AS [SP Name],
colid as Chunk,
cast(syscomments.text as text) as Proc_Def
from sysobjects inner join syscomments
on sysobjects.id = syscomments.id
where xtype = 'P'
-- and sysobjects.name in(listofnames)
I think that I'll assemble the chunks into one record in a vb.net dataset table before passing the table to a crystal report for display.
For grins, I ran the following code:
select colid, count(colid) AS NumberOfChunks
from sysobjects inner join syscomments
on sysobjects.id = syscomments.id
where xtype = 'P'
and colid > 1
group by colid
I had one sp that was 46 chunks!!! Almost 200 with 2 chunks, 100 with 3 (also included in the 200), 22 with six.
Thanks again.
June 21, 2006 at 8:47 am
Why not just script the stored procedures to a text file? Then as you move thru the procedures, you can add your own comments to the text file and easily find the other procedures that are used by searching for their Create statements.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply