How to check if a table exists in the SQL server

Before we create a new table or perform some CRUD operations on a table, it is important to check if it exists in the database.

In this shot, we discuss a few approaches to check if a particular table exists in the SQL Server.

For the entire shot, we will assume that the name of our table is Shots and the name of our database is Edpresso.

1. Using OBJECT_ID()

The OBJECT_ID() function checks if theShots table exists in the database Edpresso database.

USE [Edpresso]

GO

IF OBJECT_ID(N'dbo.Shots', N'U') IS NOT NULL

BEGIN

    PRINT 'Table Exist'

END

2. Using sys.Objects

We can use sys.Objects to check if the table Shots table exists in the Edpresso database.

USE [Edpresso]

GO

IF EXISTS(SELECT 1 FROM sys.Objects WHERE Object_id = OBJECT_ID(N'dbo.Shots') AND Type = N'U')

BEGIN

   PRINT 'Table Exist'

END

3. Using INFORMATION_SCHEMA.TABLES

We can use INFORMATION_SCHEMA.TABLES to check if the Shots table exists in the Edpresso database.

USE [Edpresso]

GO

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'Shots')

BEGIN

    PRINT 'Table Exist'

END

4. Using sys.Tables

We can use sys.Tables() to check if the Shots table exists in the Edpresso database.

USE [Edpresso]

GO

IF EXISTS(SELECT 1 FROM sys.Tables WHERE  Name = N'Shots' AND Type = N'U')

BEGIN

    PRINT 'Table Exist'

END
New on Educative
Learn any Language for FREE all September 🎉
For the entire month of September, get unlimited access to our entire catalog of beginner coding resources.
🎁 G i v e a w a y
30 Days of Code
Complete Educative’s daily coding challenge every day in September, and win exciting Prizes.

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)