Advertisement
Advertisement


Check if table exists in SQL Server


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

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

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

2018/10/17
1
1168
10/17/2018 7:03:59 PM

Accepted Answer

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
2012/05/03
1369
5/3/2012 10:26:39 AM

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
2010/01/28

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

IF OBJECT_ID('*objectName*', 'U') IS NOT NULL 
2015/06/15

Please see the below approaches,

Approach 1: Using INFORMATION_SCHEMA.TABLES view

We can write a query like below to check if a Customers Table exists in the current database.

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'Customers')
BEGIN
    PRINT 'Table Exists'
END

Approach 2: Using OBJECT_ID() function

We can use OBJECT_ID() function like below to check if a Customers Table exists in the current database.

IF OBJECT_ID(N'dbo.Customers', N'U') IS NOT NULL
BEGIN
    PRINT 'Table Exists'
END

Approach 3: Using sys.Objects Catalog View

We can use the Sys.Objects catalog view to check the existence of the Table as shown below:

IF EXISTS(SELECT 1 FROM sys.Objects WHERE  Object_id = OBJECT_ID(N'dbo.Customers') AND Type = N'U')
BEGIN
   PRINT 'Table Exists'
END

Approach 4: Using sys.Tables Catalog View

We can use the Sys.Tables catalog view to check the existence of the Table as shown below:

 IF EXISTS(SELECT 1 FROM sys.Tables WHERE  Name = N'Customers' AND Type = N'U')
 BEGIN
      PRINT 'Table Exists'
 END

Approach 5: Avoid Using sys.sysobjects System table

We should avoid using sys.sysobjects System Table directly, direct access to it will be deprecated in some future versions of the Sql Server. As per Microsoft BOL link, Microsoft is suggesting to use the catalog views sys.objects/sys.tables instead of sys.sysobjects system table directly.

  IF EXISTS(SELECT name FROM sys.sysobjects WHERE Name = N'Customers' AND xtype = N'U')
  BEGIN
     PRINT 'Table Exists'
  END

referred from: http://sqlhints.com/2014/04/13/how-to-check-if-a-table-exists-in-sql-server/

2014/12/22

Looking for a table on a different database:

if exists (select * from MyOtherDatabase.sys.tables where name = 'MyTable')
    print 'Exists'
2012/03/15

Just wanted to mention one situation where it would probably be a little easier to use the OBJECT_ID method. The INFORMATION_SCHEMA views are objects under each database-

The information schema views are defined in a special schema named INFORMATION_SCHEMA. This schema is contained in each database.

https://msdn.microsoft.com/en-us/library/ms186778.aspx

Therefore all tables you access using

IF EXISTS (SELECT 1 
           FROM [database].INFORMATION_SCHEMA.TABLES 
           WHERE TABLE_TYPE='BASE TABLE' 
           AND TABLE_NAME='mytablename') 
   SELECT 1 AS res ELSE SELECT 0 AS res;

will only reflect what is in [database]. If you wanted to check if tables in another database exist, without dynamically changing the [database] each time, OBJECT_ID will let you do this out of the box. Ex-

IF OBJECT_ID (N'db1.schema.table1', N'U') IS NOT NULL 
   SELECT 1 AS res ELSE SELECT 0 AS res;

works just as well as

IF OBJECT_ID (N'db2.schema.table1', N'U') IS NOT NULL 
   SELECT 1 AS res ELSE SELECT 0 AS res;

SQL SERVER 2016 Edit:

Starting with 2016, Microsoft simplified the ability to check for non-existent objects prior to dropping, by adding the if exists keywords to drop statements. For example,

drop table if exists mytablename

will do the same thing as OBJECT_ID / INFORMATION_SCHEMA wrappers, in 1 line of code.

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/03/drop-if-exists-new-thing-in-sql-server-2016/

2017/05/09

Source: https://stackoverflow.com/questions/167576
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]