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 to Code
Learn any Language as a beginner
Develop a human edge in an AI powered world and learn to code with AI from our beginner friendly catalog
🏆 Leaderboard
Daily Coding Challenge
Solve a new coding challenge every day and climb the leaderboard

Free Resources

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