Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

if object exists 1

Status
Not open for further replies.

ranta

Programmer
Jun 24, 2003
30
From within a stored procedure I am trying to detect whether a table exists, however the table is in a different database to the SP, I have tried using the following code but this only works where the table and the SP are in the same database...

if exists (select * from dbo.sysobjects where id = object_id('NA_052003_BP') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

Any ideas???

Thanks
 
You need to use the full naming convention, except for the server part (unless the databases are on different servers).

database.owner.table


-SQLBill
 
I have already tried that;

if exists (select * from dbo.sysobjects where id = object_id('GLOBAL_POS.DBO.NA_052003_BP') and OBJECTPROPERTY(id, N'IsUserTable') = 1)


This still fails.. it works if I run it locally...
 
you're getting the id from the other database but checking your current database's sysobjects table. and i think OBJECTPROPERTY only works in the current database. this should work.

if exists (select * from GLOBAL_POS.dbo.sysobjects where id = object_id('GLOBAL_POS.DBO.NA_052003_BP') and xtype = 'u')
 
Discord is correct. According to Books OnLine - OBJECTPROPERTY returns information about objects in the current database.

-SQLBill
 
How about this:

Code:
IF EXISTS(select table_name
    from GLOBAL_POS.Information_Schema.Tables
    where table_name = 'NA_052003_BP') ...


--Angel [rainbow]
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.
 
if exists (select * from GLOBAL_POS.dbo.sysobjects where name = 'NA_052003_BP' and xtype = 'u')

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
 
It seems safer to query the Information_Schema views. Microsoft says that the system tables will change in the future, but the Information_Schema views will not.

--Angel [rainbow]
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top