How do I get the schema of a stored procedure

  • Thread starter Thread starter David Thielen
  • Start date Start date
D

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
 
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
 
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
Click to expand...


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

Cubicle Wars - http://www.windwardreports.com/film.htm
 
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)?
Click to expand...

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
 
Back
Top