[dba-SQLServer] If Exists puzzle

This is what I use:

if (exists (select *
            from YourDataBaseNameHere.dbo.sysobjects
            where (name = N'SomeTable') and (type = 'U') and (uid =
user_id('dbo'))))
PRINT 'Yes'
ELSE
PRINT 'No'

--for #temptables:
if (exists (select * from TEMPdb.dbo.sysobjects
            WHERE (name = N'##tmpBatchUpload') and
                  ( xtype = 'U') and
                  (uid = user_id('dbo'))
    ))
    --drop table dbo.SomeTable
PRINT 'Yes'
ELSE
PRINT 'No'


--Search all databases for table:
Exec sp_MSforeachdb
'Select ''?'' as Dbname, *
>From ?.INFORMATION_SCHEMA.Tables
where TABLE_NAME like ''YourTableNameHere'''

On Sun, Jul 15, 2012 at 6:54 AM, Arthur Fuller <fuller.artful at gmail.com>wrote:

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