SQL SERVER: CHECK IF TABLE EXISTS

Pravesh Singh

Total Post:101

Points:709
Posted by  Pravesh Singh
 1099  View(s)
Ratings:
Rate this:
Hi!

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 possible ways of doing it. Which 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'".$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.

Thanks !
  1. AVADHESH PATEL

    Post:604

    Points:4228
    Re: SQL Server: Check if table exists

    Hi Pravesh!


    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

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!