Check if table exists in SQL Server

Asked 2008-10-03T16:00:33.803

1467

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.

Here are two possible ways of doing it. Which one 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.

Edit Report

2 Answers

359

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

answered 2010-01-28T14:50:03.297

IF EXISTS 
(
    SELECT   * 
    FROM     sys.objects 
    WHERE    object_id = OBJECT_ID(N'[dbo].[Mapping_APCToFANavigator]') 
             AND 
             type in (N'U')
)
BEGIN

    -- Do whatever you need to here.

END

Here in the above code, the table name is Mapping_APCToFANavigator.

answered 2011-04-07T10:39:34.297

Check if table exists in SQL Server

2 Answers

Your Answer