ADO, retrieve content of stored procedure

[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

 

[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.

Found the answer myself:

On Error GoTo myErr

Dim obj As AccessObject, dbs As Object, strTbl As String, prp As
Property, str As String
Dim con As ADODB.Connection, fldLoop As ADODB.Field, rs As
Recordset
Set con = CurrentProject.Connection

Set rs = New ADODB.Recordset
Set dbs = Application.CurrentData

'--cut, code left out--

For Each obj In dbs.AllStoredProcedures
str = ""
If obj.Type = acStoredProcedure Then
strTbl = obj.Name
rs.Open "exec sp_helptext '" & strTbl & "'", con
If Not rs.EOF Then
Do Until rs.EOF
str = str & Trim(rs!Text)
rs.MoveNext
Loop
If InStr(1, str, "RammeKr") > 0 Then
Debug.Print obj.Name
End If
End If
rs.Close
End If
Next obj


On Error Resume Next
Set obj = Nothing
rs.Close
con.Close

Set rs = Nothing
Set con = Nothing


myExit:
'MsgBox "Done"
Exit Sub

myErr:
Debug.Print "Error on " & strTbl
Resume Next