OBJECTPROPERTY problem

Why does the first query work properly and return 1 for the IsMsShipped
column when the second and third query do not?
use Northwind
go
SELECT convert(varchar,name) as ObjectName, id, OBJECTPROPERTY(id,
N'IsMSShipped') as IsMSShipped
FROM sysobjects
WHERE name='dt_adduserobject'
use Model
go
SELECT convert(varchar,name) as ObjectName, id, OBJECTPROPERTY(id,
N'IsMSShipped') as IsMSShipped
FROM Northwind..sysobjects
WHERE name='dt_adduserobject'
SELECT convert(varchar,name) as ObjectName, id,
OBJECTPROPERTY(1157579162, N'IsMSShipped') as IsMSShipped
FROM Northwind..sysobjects
WHERE name='dt_adduserobject'
'dt_adduserobject' is just an example of an object that I'd like to
exclude from my results using the IsMSShipped property. I'm querying
sysobjects for every database from a stored proc that uses dynamic sql
to supply the database name. I need to exclude system objects without
resorting to sysobjects.name not like 'dt%'. Unfortunately, this
OBJECTPROPERTY function ignores the database in my FROM clause. Any
ideas?Because OBJECTPROPERTY is resolved to the current datanase. So, it is
trying to find a local object in Model, with whatever id is assigned to
dt_adduserobject in the Northwind database.
<drink.the.koolaid@.gmail.com> wrote in message
news:1140185167.395442.186560@.f14g2000cwb.googlegroups.com...
> Why does the first query work properly and return 1 for the IsMsShipped
> column when the second and third query do not?
> use Northwind
> go
> SELECT convert(varchar,name) as ObjectName, id, OBJECTPROPERTY(id,
> N'IsMSShipped') as IsMSShipped
> FROM sysobjects
> WHERE name='dt_adduserobject'
>
> use Model
> go
> SELECT convert(varchar,name) as ObjectName, id, OBJECTPROPERTY(id,
> N'IsMSShipped') as IsMSShipped
> FROM Northwind..sysobjects
> WHERE name='dt_adduserobject'
> SELECT convert(varchar,name) as ObjectName, id,
> OBJECTPROPERTY(1157579162, N'IsMSShipped') as IsMSShipped
> FROM Northwind..sysobjects
> WHERE name='dt_adduserobject'
> 'dt_adduserobject' is just an example of an object that I'd like to
> exclude from my results using the IsMSShipped property. I'm querying
> sysobjects for every database from a stored proc that uses dynamic sql
> to supply the database name. I need to exclude system objects without
> resorting to sysobjects.name not like 'dt%'. Unfortunately, this
> OBJECTPROPERTY function ignores the database in my FROM clause. Any
> ideas?
>|||Read under the Remarks of the BOL (OBJECTPROPERTY) what the cause is:
"The Database Engine assumes that object_id is in the current database
context."
HTH, Jens Suessmeyer.|||Is there any way to change the "current database context" from within a
procedure? I think I've tried "USE Northwind GO" without
success...
Can I somehow create my own OBJECTPROPERTY function that is not
sensitive to the current database context?
Can I use some other method to determine if an object is a "system
object" that doesn't depend on the current database context?
Thanks for your help.|||> Is there any way to change the "current database context" from within a
> procedure?
No. Only option would be to construct the query in a string and use dynamic
SQL. This has several
drawbacks, see the articles at www.sommarskog.se.

> Can I somehow create my own OBJECTPROPERTY function that is not
> sensitive to the current database context?
I don't think that is possible, taking into account the limitations of what
you can do inside a
function (no dynamic SQL etc), and the fact that a function is database spec
ific, not global.

> Can I use some other method to determine if an object is a "system
> object" that doesn't depend on the current database context?
You can check against some undocumented status column in the sysobjects tabl
e. Do some searching etc
and you will surely find out how to do it. But be aware that this code will
*not* port to 2005 as
the system tables has been reworked and undocumented stuff are not ported to
the compatibility
views. In 2005, you have much better ways of doing this though the new catal
og views.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<drink.the.koolaid@.gmail.com> wrote in message
news:1140199085.871112.237720@.g14g2000cwa.googlegroups.com...
> Is there any way to change the "current database context" from within a
> procedure? I think I've tried "USE Northwind GO" without
> success...
> Can I somehow create my own OBJECTPROPERTY function that is not
> sensitive to the current database context?
> Can I use some other method to determine if an object is a "system
> object" that doesn't depend on the current database context?
> Thanks for your help.
>|||I haven't tried it but you can probably use dynamic sql (sp_executesql to be
exact) and an output parameter to get what you want. You can't put a GO but
I think you can do something like this:
USE YourOtherDB
SET @.X = OBJECTPROPERTY()
wrapped in dynamic sql where @.x is the putput param.
http://www.support.microsoft.com/?id=262499 Using OutPut Params &
sp_executeSql
Andrew J. Kelly SQL MVP
<drink.the.koolaid@.gmail.com> wrote in message
news:1140199085.871112.237720@.g14g2000cwa.googlegroups.com...
> Is there any way to change the "current database context" from within a
> procedure? I think I've tried "USE Northwind GO" without
> success...
> Can I somehow create my own OBJECTPROPERTY function that is not
> sensitive to the current database context?
> Can I use some other method to determine if an object is a "system
> object" that doesn't depend on the current database context?
> Thanks for your help.
>|||I couldn't make that work via dynamic sql via EXECUTE(N'Use Northwind')
I'm also having trouble locating an informative post about
sysobjects.status... :(|||> I'm also having trouble locating an informative post about
> sysobjects.status... :(
Exactly what is it that you need to know? The type column in sysobjects is v
ery informative, for
example. And it id documented.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<drink.the.koolaid@.gmail.com> wrote in message
news:1140274997.951001.77840@.g43g2000cwa.googlegroups.com...
>I couldn't make that work via dynamic sql via EXECUTE(N'Use Northwind')
> I'm also having trouble locating an informative post about
> sysobjects.status... :(
>|||You have to put the USE and the code you want executed in that db scope all
in the same dynamic sql batch.
DECLARE @.X NVARCHAR(500)
SET @.X = 'USE NORTHWIND
SELECT convert(varchar,[name]) as [ObjectName], [id], OBJECTPROPERTY([id],
N''IsMSShipped'') as [IsMSShipped]
FROM sysobjects
WHERE name=''Customers'''
EXEC(@.x)
Andrew J. Kelly SQL MVP
<drink.the.koolaid@.gmail.com> wrote in message
news:1140274997.951001.77840@.g43g2000cwa.googlegroups.com...
>I couldn't make that work via dynamic sql via EXECUTE(N'Use Northwind')
> I'm also having trouble locating an informative post about
> sysobjects.status... :(
>|||Thank you! That work's great.