How to check if a table already exists in the database?

Question

Now I am using something like:

IF (object_id('table', 'U') is not null)  
AND (NOT EXISTS (SELECT * FROM [table] WHERE id = 0))  
    RETURN; 

to check if this 'table' already exists in my database but I get the next error message while running the script if there is no such table in the current database:

Msg 208, Level 16, State 1, Line 5
Invalid object name 'table'.

Is this correct way to check if a table exists in a database? Or may be I must change any settings of my SQL Management Studio?

ANSWER: I changed my script and everything works well:

IF (object_id('table', 'U') is not null)  
    IF (NOT EXISTS (SELECT * FROM [table] WHERE id = 0))  
        RETURN;  

Solution 1

if (object_id('tablex', 'U') is not null) 
begin 
select * from tablex  
end

works for me. If the table does not exist then compilation of that statement should be deferred (assuming that you are on SQL Server 2005+).

If you are referencing the table in anything other than the statement immediately after the if you need to use begin...end.

Updated.

You need to use

IF (object_id('table', 'U') is not null)
    IF (NOT EXISTS (SELECT * FROM [table] WHERE id = 0))
       RETURN; 

Your existing statement cannot compile as is. The whole statement gets compiled and all objects must exist at that point even if compilation of the statement has previously been deferred.

Additionally SQL Server is not guaranteed to do short circuit evaluation in any case.

Solution 2

IF EXISTS (SELECT * 
           FROM sys.objects 
           WHERE object_id = OBJECT_ID(N'table') 
           AND type in (N'U'))

I ran into similar issues found doing the query as such helped. The N meerly indicates unicode

Solution 3

This might work better for you

if (object_id('table', 'U') is not null)
  if not exists (SELECT * FROM [table] WHERE id = 0)