How do I determine system vs user stored procedures?

[David Thielen]

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

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

 

[David Thielen]

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

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

 

[David Thielen]

That's correct - that identifier is not included in the GetSchema method...



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

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

 

[David Thielen]

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

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

 

[Lingzhi Sun [MSFT]]

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 address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#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/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.