|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
OBJECTPROPERTY problemcolumn 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.kool***@gmail.com> wrote in message Show quote 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 No. Only option would be to construct the query in a string and use dynamic SQL. This has several > procedure? drawbacks, see the articles at www.sommarskog.se. > Can I somehow create my own OBJECTPROPERTY function that is not I don't think that is possible, taking into account the limitations of what you can do inside a > sensitive to the current database context? function (no dynamic SQL etc), and the fact that a function is database specific, not global. > Can I use some other method to determine if an object is a "system You can check against some undocumented status column in the sysobjects table. Do some searching etc > object" that doesn't depend on the current database context? 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 catalog views. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ <drink.the.kool***@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 -- Show quoteAndrew J. Kelly SQL MVP <drink.the.kool***@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 Exactly what is it that you need to know? The type column in sysobjects is very informative, for > sysobjects.status... :( example. And it id documented. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ <drink.the.kool***@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 mean like this?
"status int Reserved. For internal use only. " (from Transact SQL help on the sysobjects table) :( The column named "status" is documented.
-- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ <drink.the.kool***@gmail.com> wrote in message news:1140445993.086886.314660@g44g2000cwa.googlegroups.com... > You mean like this? > "status int Reserved. For internal use only. " (from Transact SQL help > on the sysobjects table) :( > Tybor,
This is the second post where you've not provided any useful information about either the status column or where the documentation you refer to can be found. You post this despite a) my assertions that I'm having trouble locating the relevant information and b) my quote from the Transact SQL help (proving that I've made attempts to read the manual) If you want to share your wisdom in this newsgroup, great! Please share a url where I can find the necessary information. Sorry. I mistyped. The column I was referring to is the xtype column in sysobjects, which is
documented in Books Online. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ <drink.the.kool***@gmail.com> wrote in message news:1141061269.596203.184990@i40g2000cwc.googlegroups.com... > Tybor, > This is the second post where you've not provided any useful > information about either the status column or where the documentation > you refer to can be found. You post this despite a) my assertions > that I'm having trouble locating the relevant information and b) my > quote from the Transact SQL help (proving that I've made attempts to > read the manual) If you want to share your wisdom in this newsgroup, > great! Please share a url where I can find the necessary information. > > This is the second post where you've not provided any useful In Tibor's defense, you mis-read the first one. He said type, not status. > information about either the status column or where the documentation > you refer to can be found. You went off on how status is reserved and for internal use only. Which it is. Cut the guy some slack, he's trying to help you. It's not his fault you don't appreciate it. In my defense (from Tibor's first post in this thread)...
"You can check against some undocumented status column..." In any case, muchas gracias to everyone. The "Use" statement inside the sql text without the seperating GO is ideal and has been working fine. I appreciate the assistance. > You mean like this? Read. Tibor said type, not status.> "status int Reserved. For internal use only. " (from Transact SQL help > on the sysobjects table) :( 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) -- Show quoteAndrew J. Kelly SQL MVP <drink.the.kool***@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... :( > Why don't you create a view. This way you can evaluate OBJECTPROPERTY in
its own database and access the correct value from whatever context you're currently in. USE master; GO CREATE TABLE dbo.foo ( bar INT ); GO CREATE VIEW dbo.Objects AS SELECT id, name, IsMsShipped = OBJECTPROPERTY(id, 'isMsShipped') FROM sysobjects; GO USE tempdb; GO SELECT id,name,isMsShipped FROM master.dbo.Objects WHERE name IN ('foo', 'sysobjects'); GO USE master; GO DROP TABLE dbo.foo; DROP VIEW dbo.Objects; GO <drink.the.kool***@gmail.com> wrote in message Show quote 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. >  |
|||||||||||||||||||||||
Other interesting topics