How do I extract Stored procedures programatically
Experts,
As a background, I'm fairly new (or obsolete) on DB development. I'm using C# to build an application that would extract the list stored procedures found in a particular DB (SQL Sever 2K5). The question is, how do I do it programatically? and by doing so, will I be able to see the whole stored procedure, names and paramenters, or only names?
Thanks
As a background, I'm fairly new (or obsolete) on DB development. I'm using C# to build an application that would extract the list stored procedures found in a particular DB (SQL Sever 2K5). The question is, how do I do it programatically? and by doing so, will I be able to see the whole stored procedure, names and paramenters, or only names?
Thanks
to select only user-defined stored procedures, use
select name from sysobjects where type='P' and category=0
select name from sysobjects where type='P' and category=0
There are a few ways that may work to perform this task, and, depending on which way you use, you may be able to get parameter information. One of the easiest method to get both the stored procedures and the parameters would be to use the query provided by YZlat above. Then, using the information in that query, you can use an ADO command object, set it up to access each individual stored procedure, then use a Parameters.Refresh action to load the parameters into the .Parameters collection. Spin through the collection, and you have all of the parameters (and their in/out status)
oh, and to retrieve the text of the stored procedure, use
SELECT text
FROM syscomments
WHERE id = (SELECT id FROM sysobjects WHERE name = 'YOUR STORED PROC NAME HERE')
ORDER BY colid
SELECT text
FROM syscomments
WHERE id = (SELECT id FROM sysobjects WHERE name = 'YOUR STORED PROC NAME HERE')
ORDER BY colid
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
YZLat.
I'll try your solution and get back later with the results.
Thanks all for your help.
I'll try your solution and get back later with the results.
Thanks all for your help.
ASKER
OK,
code works perfectly, however I'm unable to follow bhess1 recommendation to obtain the list of parameters. I have the code shown below, but I'm unable to call a refresh or update or whatever method would allow me to obtaint the parameters for the stored procedure:
SqlConnection tempConnection=GetConnection(strConn);
SqlCommand adoCmd = new SqlCommand(proc_name, tempConnection);
adoCmd.CommandType = CommandType.StoredProcedure;
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ignorance is expensive. Thanks guys for enlightening the path.
select name from sysobjects where type='P'