SQL Server: Check if table exists

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 Statement.

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

Here are two ways to start discussion:

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;

IF OBJECT_ID (N'".$table_name."', N'U') IS NOT NULL SELECT 1 AS res ELSE SELECT 0 AS res;

MySQL provides a nice SHOW TABLES LIKE '%tablename%'; statement. I am looking for something similar.

Answer 1

Using the Information Schema is the SQL Standard way to do it, so it should be used by all databases that support it.

Answer 2

We always use the OBJECT_ID style for as long as I remember

IF OBJECT_ID('*objectName*') IS NOT NULL

Answer 3

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 4

If you need to work on different databases:

DECLARE @Catalog VARCHAR(255)
SET @Catalog = 'MyDatabase'

DECLARE @Schema VARCHAR(255)
SET @Schema = 'dbo'

DECLARE @Table VARCHAR(255)
SET @Table = 'MyTable'

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES   
    WHERE TABLE_CATALOG = @Catalog 
      AND TABLE_SCHEMA = @Schema 
      AND TABLE_NAME = @Table))
BEGIN
   --do stuff
END

Answer 5

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