[dba-SQLServer] If Exists puzzle

Arthur,

Is this a temp table?

If so, it is not in your database, it is in the tempdb.

Another suggestion is to take of the 1 in SELECT 1.
A second one is to use sys.All_Objects and filter for the table 
objects and the table name.
See below:

SELECT     name, type, type_desc
FROM         sys.all_objects
WHERE     (type_desc = N'USER_TABLE') AND (name = N'AssetUser')





At 09:03 AM 7/15/2012, you wrote:
>Date: Sun, 15 Jul 2012 09:54:03 -0400
>From: Arthur Fuller <fuller.artful at gmail.com>
>To: Discussion concerning MS SQL Server
>         <dba-sqlserver at databaseadvisors.com>
>Subject: [dba-SQLServer] If Exists puzzle
>Message-ID:
>         <CAEPJO1hG=aFYvTasa_rnHijkrSTdNHSpiQQO5N96OsCdopu+XA at mail.gmail.com>
>Content-Type: text/plain; charset=ISO-8859-1
>
>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

Robert L. Stewart
www.WeBeDb.com
www.DBGUIDesign.com
www.RLStewartPhotography.com