[dba-SQLServer] If Exists puzzle

Arthur Fuller fuller.artful at gmail.com
Sun Jul 15 08:54:03 CDT 2012


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


More information about the dba-SQLServer mailing list