checking for objects in 2nd database
-
I would like to check the existance of an object in a second database.
Running a query with "use [db1]" works fine, but seeing as you cannot use 'Use' in a proc... im lost
Lets say I'm running the query from db2
Then i want to do a check on an object to see if it exists. simple right?
if exists(select * from dbo.sysobjects where id = object_id(N'[db1].[dbo].[sale]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
print 'True'
Else Print 'False'
I've also tried:
if exists(select * from db1.dbo.sysobjects where id = object_id(N'[dbo].[sale]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
print 'True'
Else Print 'False'
And finally tried:
if exists(select * from master.dbo.sysobjects where id = object_id(N'[dbo].[sale]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
print 'True'
Else Print 'False'
No Luck.
Is there any way to query sysobjects in a proc for a different db than the one you are working in?
I've stripped out the query, and I'm now debugging for the following query:
select * from dbo.sysobjects where id = object_id(N'[dbo].[sale]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
If I select the correct database or "use db1" then it works. when on a different database, no combination sof servernames/databasenames.schema.objects does the trick.
-
prefix the database name in the query.
if exists(select * from db2.dbo.sysobjects where id = object_id(N'[db2].[dbo].[sale]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Pradeep Singh -
Tried this as well. No Luck
-
select * from db2..sysobjects where name='myTablename' and xtype='U' should return a row if myTableName exists in db2 or NULL if it doesn't.
Pradeep Singh -
ok here is a generic query:
if exists(select * from msdb.dbo.sysobjects where id = object_id(N'[msdb].[dbo].[RTblClassDefs]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
print 'true'
Stand on msdb, and run the query, true is printed
Stand on any other db, run it... and nothing.
-
The problem is that "OBJECTPROPERTY(id, N'IsUserTable')" is going to check against the id in the current database, not the id in db2.
Try this:-
if exists(select * from db2.dbo.sysobjects where id = object_id(N'[db2].[dbo].[sale]') and Xtype = 'U')
-
Ian Scarlett, You da man!
Thanks!
That did the trick just nicely!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply