SQL SERVER: CHECK IF TABLE EXISTS

Goti Bandu

Total Post:119

Points:835
Posted by  Goti Bandu
 2237  View(s)
Ratings:
Rate this:
Hi Everyone!

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.

Advance Thanks!

  1. AVADHESH PATEL

    Post:604

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

    Hi Goti!

    You can used INFORMATION_SCHEMA to view table

    try as below

    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!