Hi;

I am using the following to get all stored procedures in a Sql Server
database:

cmd.CommandText = "SELECT
o.name FROM dbo.sysobjects o where (OBJECTPROPERTY(o.id,
N'IsProcedure') = 1 " +
"or
OBJECTPROPERTY(o.id, N'IsExtendedProc') = 1 or OBJECTPROPERTY(o.id,
N'IsReplProc') = 1) and " +
"o.name not like
N'#%%' and OBJECTPROPERTY(o.id, N'IsMSShipped') = 0 order by o.name";

How can I get the schema for each stored procedure?

thanks - dave

david@at-at-(E-Mail Removed)
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm

Hello Dave,

Why not use the connection.GetSchema function to retrieve the procedure and
its schema information. Mark gives a sample codes in his blog,
http://blogs.msdn.com/mab/archive/20...schema-definit
ions-of-stored-procedures-in-net.aspx

By the way, what kind of schema you are expecting? Do you mean you also
need to get the desired parameters information for each stored procedure?
If that is the case, we need to use the SqlCommandBuilder's
DeriveParameters property to retrieve that.
http://msdn.microsoft.com/en-us/libr...sqlcommandbuil
der.deriveparameters.aspx

Hope this helps! Have a nice weekend!


Best regards,
Colbert Zhou
Microsoft Newsgroup Support Team

Hi;

GetSchema("procedures") gives the schema, but not the description or
if it's a system or user proc. Schema is more important but is there
any way to get both?

For GetSchema("ProcedureParameters") it does not return if the
parameter has a default value or what that default value is. It also
does not return the description. Is there a way to get those
(especially the default)?

thanks - dave


On Fri, 26 Jun 2009 08:09:16 GMT, (E-Mail Removed)
(Colbert Zhou [MSFT]) wrote:

>Hello Dave,
>
>Why not use the connection.GetSchema function to retrieve the procedure and
>its schema information. Mark gives a sample codes in his blog,
>http://blogs.msdn.com/mab/archive/20...schema-definit
>ions-of-stored-procedures-in-net.aspx
>
>By the way, what kind of schema you are expecting? Do you mean you also
>need to get the desired parameters information for each stored procedure?
>If that is the case, we need to use the SqlCommandBuilder's
>DeriveParameters property to retrieve that.
>http://msdn.microsoft.com/en-us/libr...sqlcommandbuil
>der.deriveparameters.aspx
>
>Hope this helps! Have a nice weekend!
>
>
>Best regards,
>Colbert Zhou
>Microsoft Newsgroup Support Team


david@at-at-(E-Mail Removed)
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm

On Fri, 26 Jun 2009 16:19:49 -0600, David Thielen
<(E-Mail Removed)> wrote:

>Hi;
>
>GetSchema("procedures") gives the schema, but not the description or
>if it's a system or user proc. Schema is more important but is there
>any way to get both?
>
>For GetSchema("ProcedureParameters") it does not return if the
>parameter has a default value or what that default value is. It also
>does not return the description. Is there a way to get those
>(especially the default)?

Hi;

Any luck finding a solution for this? This is merely a "nice to have"
so if it's a lot of work - please do not work on this.

thanks - dave

david@at-at-(E-Mail Removed)
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm

On Sun, 05 Jul 2009 13:28:17 -0600, David Thielen
<(E-Mail Removed)> wrote:

>On Fri, 26 Jun 2009 16:19:49 -0600, David Thielen
><(E-Mail Removed)> wrote:
>
>>Hi;
>>
>>GetSchema("procedures") gives the schema, but not the description or
>>if it's a system or user proc. Schema is more important but is there
>>any way to get both?
>>....

Got an answer for is a schema a user or system schema part of the
question below.

thanks - dave

david@at-at-(E-Mail Removed)
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm