VBA (Excel ADODB ODBC): Get a list of stored procedures and tables from ANY database?

Question

I would like to be able to list stored procedures from an ODBC data connection using ADODB.

I have an ADO connection:

    Public DBConn As New ADODB.Connection 
    DBConn.Properties("Prompt") = adPromptAlways

The user selects an ODBC data source.

Then I want to display a list of tables and stored procedures.

Can this be done in the general case, without being specific to the SQL server or Oracle (say)?

+3

Richard Briggs Apr 28 '12 at 10:44

1 answer

Uri Goren · Answer 1 · 2012-07-28T03:23:32+0000

Most databases have a collection of metadata called INFORMATION_SCHEMA (AKA SQL-92 standard).

If the INFORMATION_SCHEMA.ROUTINES view exists, you can list stored procedures with it.