[dba-SQLServer] If Exists puzzle

+1

-Francisco


On Sun, Jul 15, 2012 at 12:13 PM, David McAfee <davidmcafee at gmail.com>wrote:

> 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
> >
> >
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>