Retrieve the stored procedure script with ADO.NET

[Bill]

My SQL Server 2000 database has a number of user defined stored
procedures. I need a C# program (or if necessary, a database script)
to do the following:

1. Get a list of the stored procedures.
2. For each stored procedure, retrieve the SQL of this procedure to
text format and store it in a text file.

Does ADO.NET have any built-in method to do that? Thank you!

Bill

 

[Alejandro Mesa]

Check the view information_chema.routines, there you will find what you
need. Just remember that when you create a sp with the WITH ENCRYPTION
option, you will not have the option to see the definition (in the view,
routine_definition will have null).



AMB

 

[William Ryan eMVP]

Bill:

My SQL Server 2000 database has a number of user defined stored
procedures. I need a C# program (or if necessary, a database script)
to do the following:

1. Get a list of the stored procedures.
2. For each stored procedure, retrieve the SQL of this procedure to
text format and store it in a text file.

Does ADO.NET have any built-in method to do that? Thank you!

Bill

Ultimately you have two way, SQLDMO library and T-SQL. You can use them
both too, although I doubt you'd want to ...


If you query the Sysobjects tables, Stored Procs will have an XType of 'P'.
You'd query the Sysobjects table of the Database that's holding the procs
you are interested in .

Similarly, there's a system stored procedure. sp_Stored_Procedures which
will return the name and owner among other things. You can further filter
this by specifing the name (or use wildcards to guess) the owner or the
qualifier. In your case, it doesn't appear that you'd need any of these
filters.

Now, to get the text of the stored proc, you can do a few things. Through
T-SQL you can get a list of your procs, then loop through them and pass each
one's name to sp_helptext 'storedprocname'

You can do the same using the SQLDMO library and use Object.Text to retieve
the stored procs text. T-SQL Is pretty straightforward, but to access this
programatically SQLDMO may be a bit easier overall, it really depends on
your experience with SQL Server and the Library

 

[Bill]

Thank you William for the reply. Your answer should be sufficent for
me to continue my task.

Bill

 

[Bill]

Thank you for the reply. I went through the user database with
Enterprise Manager but did not find information_chema.routines. Is it
in any system tables?

 

[Tibor Karaszi]

The INFORMATION_SCHEMA views exists in master physically, but they behave
like they exist in all databases (pretty much like the sp_ procedures in
master). They are all documented in Books Inline.