[dba-SQLServer] If Exists puzzle

The name column of sys.tables doesn't include the schema name, i.e. just
'MyTable' not 'dbo.MyTable'.
Asger
----- Original meddelelse -----

> Fra: Arthur Fuller <fuller.artful at gmail.com>
> Til: Discussion concerning MS SQL Server
> <dba-sqlserver at databaseadvisors.com>
> Dato: Søn, 15. jul 2012 15:54
> Emne: [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