if object exists 1

[ranta]

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

 

[SQLBill]

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

database.owner.table


-SQLBill

 

[ranta]

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...

 

[Discord]

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')

 

[SQLBill]

Discord is correct. According to Books OnLine - OBJECTPROPERTY returns information about objects in the current database.

-SQLBill

 

[JohnDTampaBay]

How about this:

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

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[nigelrivett]

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.

 

[JohnDTampaBay]

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
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.