[dba-SQLServer] If Exists puzzle

Wouldn't it just be easier to run a Delete preceded by an On Error?

Susan H.


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