ADO - Listing Stored Procedures???

[omarswan]

Hi,
Is there a way to list all the stored procedures using ADO or by any other method?

[hayessj]

You ac try the code below, it works for me with SQL Server 7 but be aware of the following from MSDN

"Providers are not required to support all of the OLE DB standard schema queries. Specifically, only adSchemaTables, adSchemaColumns, and adSchemaProviderTypes are required by the OLE DB specification. However, the provider is not required to support the Criteria constraints listed above for those schema queries."


Dim cnDB As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sConnect As String
Dim i As Integer

sConnect = "[Provider=SQLOLEDB];driver={SQL Server};Server=IDEE01;database=Northwind;Trusted Connection=true;"

cnDB.Open sConnect
Set rs = cnDB.OpenSchema(adSchemaProcedures)
Do Until rs.EOF
For i = 0 To rs.Fields.Count - 1
Debug.Print rs.Fields(i).Name & "-" & rs.Fields(i).Value
Next
rs.MoveNext
Loop

[Serge]

If you have SQL Sever installed then you have an ActiveX DLL called SQLDMO. This COM object is a wrapper around SQL Server. It has practicaly every piece of functionality that SQL Server provides.
So, if you do have it installed, then you can do this:

First add a reference to Microsoft SQLDMO Object Library. Also, add a ListBox.
Then use this code:

Private Sub Command1_Click()
    Dim oSQL As New SQLDMO.SQLServer
    Dim oDB As SQLDMO.Database
    Dim i As Integer
    
    oSQL.Connect "MyServer", "UserName", "Password"
    Set oDB = oSQL.Databases("YouDBName")
    For i = 0 To oDB.StoredProcedures.Count - 1
        List1.AddItem oDB.StoredProcedures(i).Name
    Next
End Sub

Where:
MyServer is a name of the Server
UserName is a UserName
Password is a valid password