Check if table exists in SQL Server

Question

I want this to be the last word on how to use SQL Statements in SQL Server 2000/2005 to determine whether a table is present.

Here are two options for how to go about it. Which one is the accepted practice or ideal method?

First way:

IF EXISTS (SELECT 1 
           FROM INFORMATION_SCHEMA.TABLES 
           WHERE TABLE_TYPE='BASE TABLE' 
           AND TABLE_NAME='mytablename') 
   SELECT 1 AS res ELSE SELECT 0 AS res;

Second way:

IF OBJECT_ID (N'mytablename', N'U') IS NOT NULL 
   SELECT 1 AS res ELSE SELECT 0 AS res;

MySQL provides the simple:

SHOW TABLES LIKE '%tablename%'; 

The statement. I am looking for something similar.

Answer 1

It is always advisable to utilise an INFORMATION SCHEMA view for queries like these. These views seldom vary from version to version and are (mostly) common across many different databases.

To check if a table exists use:

IF (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'TheSchema' 
                 AND  TABLE_NAME = 'TheTable'))
BEGIN
    --Do Stuff
END

I hope this helps you.