Getting Stored Procedure Information from .Net

Question

Hi,
I am trying to get some data relevant to a stored procedure (or funtion) back from a database using .Net. The first thing I need to be able to do, is get the stored proc from the database and turn it into string format. The information I need is: The return set of columns, tables used within the SP, Stored Procedures called from the SP. The only way of doing this at the moment that i can think of, is though parsing the text and looking for keyword matches. Is there a better way of doing this?

Any ideas?

Thanks.

Answer 1

Once you have the SPROC as text, you can use a SQL Parser.

There are several for .NET- see the answers to this SO question (Parsing SQL in .NET).

Answer 2

Assuming you're aware that the returned tables may be dependant on the inputs to the function, so there won't always be a single output for a given stored procedure, but...

To get a list of stored procedures you can call sp_help. You can then get the source of the stored procedure using sp_helptext (with @objname being the name of the stored procedure).

To find the referenced tables, columns, et al, you'd be wanting to parse the SQL script - you could do this yourself, or there are more established projects such as ANTLR that may simplify matters for you.

Answer 3

If you are using SQL Server, you can also try the sp_depends stored procedure

EXEC sp_depends @objname = N'sproc_name' ;