How to identify User Objects within sysobjects ?

I am querying master..sysobjects table for user defined objects. I have
been relying on category field to show if a given object is a user defined
object or system object. My understanding was that a category value of
zero (0) is a user object. Apparently this is not always true. I have
seen value 16 for some. Where this is documented if any ? BOL does not
talk about the values. Or may be there is a better way to find this out
other than Category field.
I appreciate in advance for any suggestions.
MacYou can use the OBJECTPROPERTY function for this:
SELECT <column list>
FROM sysobjects
WHERE OBJECTPROPERTY(id, 'IsMSShipped') = 0
--
Jacco Schalkwijk
SQL Server MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:conj65$1o9g$1@.si05.rsvl.unisys.com...
>I am querying master..sysobjects table for user defined objects. I have
> been relying on category field to show if a given object is a user defined
> object or system object. My understanding was that a category value of
> zero (0) is a user object. Apparently this is not always true. I have
> seen value 16 for some. Where this is documented if any ? BOL does not
> talk about the values. Or may be there is a better way to find this out
> other than Category field.
> I appreciate in advance for any suggestions.
> Mac
>|||"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:conj65$1o9g$1@.si05.rsvl.unisys.com...
>I am querying master..sysobjects table for user defined objects. I have
> been relying on category field to show if a given object is a user defined
> object or system object. My understanding was that a category value of
> zero (0) is a user object. Apparently this is not always true. I have
> seen value 16 for some. Where this is documented if any ? BOL does not
> talk about the values. Or may be there is a better way to find this out
> other than Category field.
> I appreciate in advance for any suggestions.
> Mac
>
Are you looking for specific objects?
SELECT Name from sysobjects where TYPE = 'U' for tables, 'P' for procs
etc.
I'm not sure if that will help you out or not.
Rick Sawtell
MCT, MCSD, MCDBA|||Excellent! Thanks Jacco for the reply. That must be it. I should check to
see if this is supported in earlier releases as well.
Mac
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23wkkjWJ2EHA.2676@.TK2MSFTNGP12.phx.gbl...
> You can use the OBJECTPROPERTY function for this:
> SELECT <column list>
> FROM sysobjects
> WHERE OBJECTPROPERTY(id, 'IsMSShipped') = 0
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:conj65$1o9g$1@.si05.rsvl.unisys.com...
> >I am querying master..sysobjects table for user defined objects. I have
> > been relying on category field to show if a given object is a user
defined
> > object or system object. My understanding was that a category value of
> > zero (0) is a user object. Apparently this is not always true. I have
> > seen value 16 for some. Where this is documented if any ? BOL does not
> > talk about the values. Or may be there is a better way to find this
out
> > other than Category field.
> >
> > I appreciate in advance for any suggestions.
> > Mac
> >
> >
>|||Thanks Rick. I was looking for user-stored procedures in the master db. I
can use the TYPE to filter the object types but to know whether it is system
defined or user defined object, I received an answer that ObjectProperty
function can be used to find that out.
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:uCHA2XJ2EHA.2624@.TK2MSFTNGP11.phx.gbl...
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:conj65$1o9g$1@.si05.rsvl.unisys.com...
> >I am querying master..sysobjects table for user defined objects. I have
> > been relying on category field to show if a given object is a user
defined
> > object or system object. My understanding was that a category value of
> > zero (0) is a user object. Apparently this is not always true. I have
> > seen value 16 for some. Where this is documented if any ? BOL does not
> > talk about the values. Or may be there is a better way to find this
out
> > other than Category field.
> >
> > I appreciate in advance for any suggestions.
> > Mac
> >
> >
> Are you looking for specific objects?
> SELECT Name from sysobjects where TYPE = 'U' for tables, 'P' for procs
> etc.
> I'm not sure if that will help you out or not.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||> I was looking for user-stored procedures in the master db.
Safer to use INFORMATION_SCHEMA in the current DB than to use sysobjects in
the master db (sysobjects is going away eventually, and it is not
recommended to use these tables directly if you can avoid it).
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='PROCEDURE'
AND OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+'.'+ROUTINE_NAME),
'IsMSShipped') = 0
--
http://www.aspfaq.com/
(Reverse address to reply.)
I
> can use the TYPE to filter the object types but to know whether it is
system
> defined or user defined object, I received an answer that ObjectProperty
> function can be used to find that out.
> "Rick Sawtell" <quickening@.msn.com> wrote in message
> news:uCHA2XJ2EHA.2624@.TK2MSFTNGP11.phx.gbl...
> >
> > "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> > news:conj65$1o9g$1@.si05.rsvl.unisys.com...
> > >I am querying master..sysobjects table for user defined objects. I
have
> > > been relying on category field to show if a given object is a user
> defined
> > > object or system object. My understanding was that a category value
of
> > > zero (0) is a user object. Apparently this is not always true. I
have
> > > seen value 16 for some. Where this is documented if any ? BOL does
not
> > > talk about the values. Or may be there is a better way to find this
> out
> > > other than Category field.
> > >
> > > I appreciate in advance for any suggestions.
> > > Mac
> > >
> > >
> >
> > Are you looking for specific objects?
> >
> > SELECT Name from sysobjects where TYPE = 'U' for tables, 'P' for procs
> > etc.
> >
> > I'm not sure if that will help you out or not.
> >
> > Rick Sawtell
> > MCT, MCSD, MCDBA
> >
> >
> >
>|||Thanks. I should look into that.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uVER44J2EHA.2316@.TK2MSFTNGP15.phx.gbl...
> > I was looking for user-stored procedures in the master db.
> Safer to use INFORMATION_SCHEMA in the current DB than to use sysobjects
in
> the master db (sysobjects is going away eventually, and it is not
> recommended to use these tables directly if you can avoid it).
> SELECT ROUTINE_NAME
> FROM INFORMATION_SCHEMA.ROUTINES
> WHERE ROUTINE_TYPE='PROCEDURE'
> AND OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+'.'+ROUTINE_NAME),
> 'IsMSShipped') = 0
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
> I
> > can use the TYPE to filter the object types but to know whether it is
> system
> > defined or user defined object, I received an answer that
ObjectProperty
> > function can be used to find that out.
> >
> > "Rick Sawtell" <quickening@.msn.com> wrote in message
> > news:uCHA2XJ2EHA.2624@.TK2MSFTNGP11.phx.gbl...
> > >
> > > "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> > > news:conj65$1o9g$1@.si05.rsvl.unisys.com...
> > > >I am querying master..sysobjects table for user defined objects. I
> have
> > > > been relying on category field to show if a given object is a user
> > defined
> > > > object or system object. My understanding was that a category value
> of
> > > > zero (0) is a user object. Apparently this is not always true. I
> have
> > > > seen value 16 for some. Where this is documented if any ? BOL does
> not
> > > > talk about the values. Or may be there is a better way to find
this
> > out
> > > > other than Category field.
> > > >
> > > > I appreciate in advance for any suggestions.
> > > > Mac
> > > >
> > > >
> > >
> > > Are you looking for specific objects?
> > >
> > > SELECT Name from sysobjects where TYPE = 'U' for tables, 'P' for
procs
> > > etc.
> > >
> > > I'm not sure if that will help you out or not.
> > >
> > > Rick Sawtell
> > > MCT, MCSD, MCDBA
> > >
> > >
> > >
> >
> >
>