Check if table exists in SQL Server

Question

I would like this to be the ultimate discussion on how to check if a table exists in SQL Server 2000/2005 using SQL Statements.

When you Google for the answer, you get so many different answers. Is there an official/backward and forward compatible way of doing it?

Here are two possible ways of doing it. Which one among the two is the standard/best way of doing it?

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%';  

statement. I am looking for something similar.

Answer 1

For queries like this it is always best to use an INFORMATION_SCHEMA view. These views are (mostly) standard across many different databases and rarely change from version to version.

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 

Answer 2

Also note that if for any reason you need to check for a temporary table you can do this:

if OBJECT_ID('tempdb..#test') is not null  --- temp table exists