I am using:

DataTable schemaTable = conn.GetSchema("procedures");
int ordSchema = schemaTable.Columns.IndexOf("SPECIFIC_SCHEMA");
int ordName = schemaTable.Columns.IndexOf("SPECIFIC_NAME");

And the DataRow's returned have nothing in them that identifies a
stored procedure as a user one vs a system one.

Even weirder, in SqlServer 2000 this returns just user stored
procedures while in SqlServer 2005 it returns system ones that start
with dt_ but not the ones that start with sp_.

Any suggestions? I would like to be able to get just user ones, just
system ones, and all (3 options).

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, 5 Jul 2009 19:50:37 +0100, "Mark Rae [MVP]"
<(E-Mail Removed)> wrote:

>SELECT
> s.name AS schema_name,
> p.name AS procedure_name
>FROM
> sys.schemas s
> INNER JOIN sys.procedures p ON s.schema_id = p.schema_id
>WHERE
> p.is_ms_shipped = 0 -- this is the identifier
>ORDER BY
> s.name,
> p.name

THANK YOU!!!!

Works great - 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, 5 Jul 2009 19:50:37 +0100, "Mark Rae [MVP]"
<(E-Mail Removed)> wrote:

>"David Thielen" <(E-Mail Removed)> wrote in message
>news(E-Mail Removed)...
>
>> DataTable schemaTable = conn.GetSchema("procedures");
>> int ordSchema = schemaTable.Columns.IndexOf("SPECIFIC_SCHEMA");
>> int ordName = schemaTable.Columns.IndexOf("SPECIFIC_NAME");
>>
>> And the DataRows returned have nothing in them that identifies a
>> stored procedure as a user one vs a system one.
>
>That's correct - that identifier is not included in the GetSchema method...
>
>
>> Any suggestions? I would like to be able to get just user ones, just
>> system ones, and all (3 options).
>
>SELECT
> s.name AS schema_name,
> p.name AS procedure_name
>FROM
> sys.schemas s
> INNER JOIN sys.procedures p ON s.schema_id = p.schema_id
>WHERE
> p.is_ms_shipped = 0 -- this is the identifier
>ORDER BY
> s.name,
> p.name

I spoke too soon. I ran the following against AdventureWorks on Sql
Server 2005 and it returned only the user stored procedures (note I
eleminated the where):

SELECT s.name AS schema_name, p.name, p.is_ms_shipped AS
procedure_name
FROM sys.schemas AS s INNER JOIN
sys.procedures AS p ON s.schema_id = p.schema_id
ORDER BY schema_name, procedure_name

Any idea why it doesn't get the system ones?

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, 5 Jul 2009 21:24:57 +0100, "Mark Rae [MVP]"
<(E-Mail Removed)> wrote:

>"David Thielen" <(E-Mail Removed)> wrote in message
>news:(E-Mail Removed)...
>
>> I spoke too soon. I ran the following against AdventureWorks on Sql
>> Server 2005 and it returned only the user stored procedures (note I
>> eleminated the where):
>
>There are no system stored procedures in AdventureWorks, at least, not in
>the copy that I just downloaded and installed...
>
>SELECT * FROM sys.procedures returns nine records, none of which is a system
>stored procedure...

Maybe I'm missing something. Using the Sql Server 2005 Management
Studio under Adventure Word, Programmability there are 9 stored
procedures and a folder titled "System Stored Procedures". And that
folder has a ton of items like sys.sp_ActiveDirectory_Obj,
sys.xp_revokelogin, etc.

Are these something else?

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

Hi David,

The select SQL command that you have used returns a list of user stored
procedures. To retrieve the system stored procedures, you can consider the
following SQL commands:
==========================================
EXEC SP_STORED_PROCEDURES @sp_owner = 'sys'
==========================================

Also, to get all the stored procedures (both user and system), we can
directly call
==========================================
EXEC SP_STORED_PROCEDURES
==========================================

If you have any questions regarding this case, please feel free to let me
know.

Have a nice day!


Best Regards,
Lingzhi Sun
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(E-Mail Removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subs...#notifications.

MSDN Managed Newsgroup support offering is for non-urgent issues where an
initial response from the community or a Microsoft Support Engineer within
2 business day is acceptable. Please note that each follow up response may
take approximately 2 business days as the support professional working with
you may need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subs.../aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.