Also check sp_helptext which you can also execute from the connection.Execute. The syntax would be "sp_helptext 'mystoredprocedure'"


>Exactly what I needed. Concise and to the point! 10 Points from me. With this (plus an if exists ... drop proc/trigger)I have the ability to give myself an on-site utility to modify the procs and triggers from the client machine. This little tool will save me hours of getting grudging admin cooperation!!
>
>Thanks,
>Joe.
>
>>>How can I display the actual code from a stored procedure in VB?
>>>
>>>My Thanks in advance,
>>-----------------------------------
>>

>>Public Function GetSProcCode(strProcedureName As String)
>>Dim cn As Object
>>Dim rs As Object
>>Dim strSelect As String
>>
>>strSelect = "select C.text from sysobjects O, syscomments C Where O.type = 'P' and C.id = O.id and text like "
>>strSelect = strSelect & "'%" & Trim(strProcedureName) & "%'"
>>
>>Set cn = CreateObject("ADODB.Connection")
>>-- open database with appropriate connection string
>>cn.Open "provider=sqloledb;data source=<YourDataSource>;initial catalog=<YourDataBase>", "<your login name>", "<your passsword>"
>>
>>Set rs = cn.execute(strSelect)
>>GetSProcCode = rs.fields("text").Value
>>
>>End Function
>>
>>
>>' and then...
>>Debug.Print GetSProcCode("up_MyProcedure")
>>

>>
>>You could also wrap this into a stored procedure in the database to lessen overhead... but to be honest, the "LIKE" makes this such a slow query, relatively speaking, that the on-the-fly compile plan isn't going to be a high percentage of the time the process will take.
>>
>>Note that this won't work if the sproc was compiled using "with encryption," which has been cracked anyway so isn't of too much use...