Home > DeveloperSection > Forums > SQL Server: Check if table exists
Pravesh Singh
Pravesh Singh

Total Post:101

Points:709
Posted on    May-06-2013 1:53 AM

 MSSQL Server MSSQL Server 
Ratings:


 1 Reply(s)
 869  View(s)
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 !


AVADHESH PATEL

Total Post:604

Points:4228
Posted on    May-06-2013 9:00 AM

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

Don't want to miss updates? Please click the below button!

Follow MindStick