ADO, how to determine content of SP

  • Thread starter Thread starter Morten Snedker
  • Start date Start date
M

Morten Snedker

I wish to loop through all my stored procedures to find any given
string contained in the procedure. For instance, using ADO I can loop
through tables and views to find af column of some given name.

I wish to do the same thing with all my stored procedures. This is
what I have:

For Each obj In dbs.AllStoredProcedures
If obj.Type = acStoredProcedure Then
Debug.Print obj.Name
End If
Next obj

So I have the name...now i want to see what's in it. How to?


Regards /Snedker
 
Hello Morten:
You wrote on Tue, 08 Jun 2004 14:02:13 +0200:

MS> So I have the name...now i want to see what's in it. How to?

stored procedures' texts are stored in the table syscomments.

Vadim
 
I wish to loop through all my stored procedures to find any given
string contained in the procedure. For instance, using ADO I can loop
through tables and views to find af column of some given name.

I wish to do the same thing with all my stored procedures. This is
what I have:

For Each obj In dbs.AllStoredProcedures
If obj.Type = acStoredProcedure Then
Debug.Print obj.Name
End If
Next obj

So I have the name...now i want to see what's in it. How to?
Click to expand...

This Stored Procedure may do what you want. I have not tested it
extensively, however.

SELECT o.name, c.text
FROM dbo.syscomments c
INNER JOIN dbo.sysobjects o
ON c.id = o.id
WHERE
(o.xtype IN ('P', 'V', 'TF', 'FN', 'IF', 'U'))
AND
(LEFT(o.name, 3) <> 'dt_')
ORDER BY o.name
 
Back
Top