Check if table exists in SQL Server

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

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

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/

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

IF OBJECT_ID('*objectName*', 'U') IS NOT NULL