[dba-SQLServer] If Exists puzzle

Maybe you could work around the problem by trying to open it with DAO and
trap the error if it doesn't exist?

Rocky
 

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Arthur
Fuller
Sent: Sunday, July 15, 2012 6:54 AM
To: Discussion concerning MS SQL Server
Subject: [dba-SQLServer] If Exists puzzle

I'm trying to check for the existence of a table, and if it does, drop it.
I did a search and came up with some code like this:

<sql>
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'dbo.MyTable') PRINT
'TableName available.'
ELSE
PRINT 'TableName not available.'
</sql>

This returns the message  "Invalid object name: sys.tables

So then I reverted to the older syntax:

<sql>
IF EXISTS(SELECT * FROM IDS.dbo.sysobjects WHERE ID =
OBJECT_ID(N'[IDS]..[dbo.MyTable'))
       PRINT 'MyTable exists'
ELSE
       PRINT 'MyTable does not exist'
</sql>

This syntax works but the result is "dbo._Tble_852Import does not exist".
Unfortunately, the table does exist. I'm in the right database when running
the query, so that's not the problem

Any idea what I'm doing wrong?
TIA,
--
Arthur
Cell: 647.710.1314

Prediction is difficult, especially of the future.
  -- Niels Bohr
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com