Get a list of available stored procedures

[roundcrisis]

Hi there:

I would like to retrive a collection with the avvaialbe stored
procedures, if there are any,
I m using an ODBC connection,
How can this be achieved?
I know u can do a select of one of the system tables with a sql
server, but what happens if i dont have a sql server?
Cheers

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

What DB r u using?

In SQL server it's kept in a system table (sysobjects IIRC).

Why are u using a ODBC connection though?

 

[zacks]

Hi there:

I would like to retrive a collection with the avvaialbe stored
procedures, if there are any,
I m using an ODBC connection,
How can this be achieved?
I know u can do a select of one of the system tables with a sql
server, but what happens if i dont have a sql server?
Cheers

As far as I am aware, there is no standard ODBC method of obtaining a
list of stored procedures on the server you are connected to. Some SQL
providers do not even support stored procedures, from what I
understand. So it depends on the actual provider itself on how to
obtain the list of SPs. For MS SQL Server it is:

select * from information_schema.routines

 

[Nicholas Paldino [.NET/C# MVP]]

Well, if you don't have a SQL Server, then you will have to use whatever
DB-specific method will give you a list of the stored procedures (there is
not one standardized way of getting stored procedures across all data
sources).

Also, the provider really doesn't matter in this case (ODBC is a
provider that can connect to many data sources, as is OLEDB) unless it is
for one product and one product only (SQL Server, for example) and even
then, it might not give you the information you want.

For SQL Server, you can simply run the sp_stored_procedures stored
procedure and it will return a list of stored procedures to you:

http://msdn2.microsoft.com/en-us/library/ms190504.aspx

 

[Ben Voigt [C++ MVP]]

Well, if you don't have a SQL Server, then you will have to use
whatever DB-specific method will give you a list of the stored procedures
(there is not one standardized way of getting stored procedures across all
data sources).

Also, the provider really doesn't matter in this case (ODBC is a
provider that can connect to many data sources, as is OLEDB) unless it is
for one product and one product only (SQL Server, for example) and even
then, it might not give you the information you want.

For SQL Server, you can simply run the sp_stored_procedures stored
procedure and it will return a list of stored procedures to you:

http://msdn2.microsoft.com/en-us/library/ms190504.aspx

Just require users with other DB engines to write sp_stored_procedures in
their database -- it's like implementing a common interface.

Then be prepared for sp_stored_procedures to show up in the list on some
databases, and filter it out before showing the user...