A common problem encountered during SQL server development is to check if a particular table exists or not. There are different approaches to solve this problem and in this blog we will list out these approaches. For illustration purpose we will be using a table named Employees.

Let us first create the Employees table:

USE SampelDB
GO
CREATE TABLE dbo.Employees
(EmpId INT, EmpName NVARCHAR(100))

1) By using INFORMATION_SCHEMA.TABLES

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

The above query will check for the existence of the Employees table throughout all the schemas in the current database.

2) By using OBJECT_ID() function

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

In the above approach ,we can also  specify database Name in case we want to check the existence of the table in a specific database.

3) By using sys.Objects Catalog View

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

Above approach uses the Sys.Objects catalog view to check the existence of the Table.

4) By using sys.Tables Catalog

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

Above approach uses the Sys.Tables catalog view to check the existence of the Table.

Hope the above article helps you in implementing checks for table existence while doing SQL server development.