How do I get the schema of a stored procedure

[David Thielen]

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@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

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

 

[Colbert Zhou [MSFT]]

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/2007/03/06/how-to-determine-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/library/system.data.sqlclient.sqlcommandbuil
der.deriveparameters.aspx

Hope this helps! Have a nice weekend!


Best regards,
Colbert Zhou
Microsoft Newsgroup Support Team

 

[David Thielen]

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

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/2007/03/06/how-to-determine-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/library/system.data.sqlclient.sqlcommandbuil
der.deriveparameters.aspx

Hope this helps! Have a nice weekend!


Best regards,
Colbert Zhou
Microsoft Newsgroup Support Team

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

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

 

[David Thielen]

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@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

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

 

[David Thielen]

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

thanks - dave

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

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