How to filter our system objects from the list

I'm using following statement to get the list of objects in given SQL
Server database (this is taken from VB6 code):
lstrSQL = "select name, refdate from dbo.sysobjects where " &
lstrFilter & " order by name"
I apply filter to narrow the results to particular type, for example
for procedures:
lstrFilter = "OBJECTPROPERTY(id, N'IsProcedure') = 1"
For functions:
lstrFilter = "OBJECTPROPERTY(id, N'IsInlineFunction') = 1
or OBJECTPROPERTY(id, N'IsScalarFunction') = 1 or OBJECTPROPERTY(id,
N'IsTableFunction') = 1"
etc.
My question is: how can I filter out the objects created by the system
(I want only the objects created by the users to be left)?
Ideally, I'd like the solution to work in SQL Server 2005 and in the
older versions.
TIA
Dariusz Dziewialtowski.Use the xtype column to exclude the objects you don't want to show:
xtype: Object type. Can be one of these object types:
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = Inlined table-function
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure
"dariusz.dziewialtowski@.gmail.com" wrote:

> I'm using following statement to get the list of objects in given SQL
> Server database (this is taken from VB6 code):
> lstrSQL = "select name, refdate from dbo.sysobjects where " &
> lstrFilter & " order by name"
> I apply filter to narrow the results to particular type, for example
> for procedures:
> lstrFilter = "OBJECTPROPERTY(id, N'IsProcedure') = 1"
> For functions:
> lstrFilter = "OBJECTPROPERTY(id, N'IsInlineFunction') = 1
> or OBJECTPROPERTY(id, N'IsScalarFunction') = 1 or OBJECTPROPERTY(id,
> N'IsTableFunction') = 1"
> etc.
> My question is: how can I filter out the objects created by the system
> (I want only the objects created by the users to be left)?
> Ideally, I'd like the solution to work in SQL Server 2005 and in the
> older versions.
> TIA
> Dariusz Dziewialtowski.
>|||select *
from sysobjects
where xtype <> 'S'
"Edgardo Valdez, MCSD, MCDBA" wrote:
> Use the xtype column to exclude the objects you don't want to show:
> xtype: Object type. Can be one of these object types:
> C = CHECK constraint
> D = Default or DEFAULT constraint
> F = FOREIGN KEY constraint
> L = Log
> FN = Scalar function
> IF = Inlined table-function
> P = Stored procedure
> PK = PRIMARY KEY constraint (type is K)
> RF = Replication filter stored procedure
> S = System table
> TF = Table function
> TR = Trigger
> U = User table
> UQ = UNIQUE constraint (type is K)
> V = View
> X = Extended stored procedure
>
> "dariusz.dziewialtowski@.gmail.com" wrote:
>|||Try,
...
and objectproperty([id], 'IsMSShipped') = 0
AMB
"dariusz.dziewialtowski@.gmail.com" wrote:

> I'm using following statement to get the list of objects in given SQL
> Server database (this is taken from VB6 code):
> lstrSQL = "select name, refdate from dbo.sysobjects where " &
> lstrFilter & " order by name"
> I apply filter to narrow the results to particular type, for example
> for procedures:
> lstrFilter = "OBJECTPROPERTY(id, N'IsProcedure') = 1"
> For functions:
> lstrFilter = "OBJECTPROPERTY(id, N'IsInlineFunction') = 1
> or OBJECTPROPERTY(id, N'IsScalarFunction') = 1 or OBJECTPROPERTY(id,
> N'IsTableFunction') = 1"
> etc.
> My question is: how can I filter out the objects created by the system
> (I want only the objects created by the users to be left)?
> Ideally, I'd like the solution to work in SQL Server 2005 and in the
> older versions.
> TIA
> Dariusz Dziewialtowski.
>|||Use the IsMSShipped object property|||Edgardo, Alejandro, Scott
Thank you for your so quick responses!
I was thinking about using IsMSShipped in the past but I was afraid
that it wouldn't work right - for example: the whole Northwind database
is shipped by Microsoft - would the flag IsMSShipped be set to TRUE for
all objects in that database in that case?
Hmm, I have to test it actually...
Again - thanks a lot for your help!
Dariusz Dziewialtowski.|||That's a good point.
Actually, the IsMSShipped property is only set for those objects that
someone set it on for. Anyone can mark an object so that the IsMSShipped bit
is true by running sp_MS_marksystemobject.
So there is really no way to know for sure what is shipped by MS and what
isn't. You'll have to come up with another way of determining which objects
you want to see and which you don't.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<dariusz.dziewialtowski@.gmail.com> wrote in message
news:1144681248.829348.140770@.i40g2000cwc.googlegroups.com...
> Edgardo, Alejandro, Scott
> Thank you for your so quick responses!
> I was thinking about using IsMSShipped in the past but I was afraid
> that it wouldn't work right - for example: the whole Northwind database
> is shipped by Microsoft - would the flag IsMSShipped be set to TRUE for
> all objects in that database in that case?
> Hmm, I have to test it actually...
> Again - thanks a lot for your help!
>
> Dariusz Dziewialtowski.
>|||Kalen,
Thanks a lot for your explanation!
Dariusz Dziewialtowski