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.

Answer 1

Looking for a table on a different database:

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

Answer 2

If anyone is trying to do this same thing in linq to sql (or especially linqpad) turn on option to include system tables and views and do this code:

let oSchema = sys.Schemas.FirstOrDefault(s=>s.Name==a.schema )
where oSchema !=null
let o=oSchema!=null?sys.Objects.FirstOrDefault (o => o.Name==a.item && o.Schema_id==oSchema.Schema_id):null
where o!=null

given that you have an object with the name in a property called item, and the schema in a property called schema where the source variable name is a

Answer 3

If you need to work on different databases:

DECLARE @Catalog VARCHAR(255)
SET @Catalog = 'MyDatabase'

DECLARE @Schema VARCHAR(255)
SET @Schema = 'dbo'

DECLARE @Table VARCHAR(255)
SET @Table = 'MyTable'

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES   
    WHERE TABLE_CATALOG = @Catalog 
      AND TABLE_SCHEMA = @Schema 
      AND TABLE_NAME = @Table))
BEGIN
   --do stuff
END

Answer 4

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/

Answer 5

In SQL Server 2000 you can try:

IF EXISTS(SELECT 1 FROM sysobjects WHERE type = 'U' and name = 'MYTABLENAME')
BEGIN
   SELECT 1 AS 'res' 
END

Answer 6

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/