sql check if table exists – Best 5 Ways to “sql check if table exists”

In sql check if table exists, here you can learn to Check whether a Table exists in SQL Server database or not with How to check if a Table exists in SQL Server or not?

SQL Check if table exists

Check if table exists Example

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'Products';

Check if table exists in SQL Server

IF (EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'TheSchema'
AND TABLE_NAME = 'Products'))
BEGIN
--Do Stuff
END

Check if table exists

IF (EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'Products'))
BEGIN
PRINT 'DB Products Table Exists'
END;
ELSE
BEGIN
PRINT 'No Products Table in DB'
END;

Output
DB Products Table Exists

Using OBJECT_ID() function

It is a returns the DB object id number if the object exists in the DB.

IF OBJECT_ID('model.dbo.Products') IS NOT NULL
BEGIN
PRINT 'DB Products Table Exists'
END;
ELSE
BEGIN
PRINT 'No Products Table in DB'
END;

Output
DB Products Table Exists

Using the sys.Objects

USE [DB_NAME]
GO
IF EXISTS(SELECT 1 FROM sys.Objects
WHERE Object_id = OBJECT_ID(N'product_table')
AND Type = N'P')
BEGIN
PRINT 'Products Table exists.'
END
ELSE
BEGIN
PRINT 'Products Table does not exist.'
END

Output
Products Table does not exists.

how to check if table exists in sql?

SELECT *
FROM sys.tables
WHERE name = 'products_tbl'
AND schema_id = SCHEMA_ID('myschema')

Also Read : sql if exists else

SQL: Check if table exists

To check if a table exists in SQL Server, you can use the INFORMATION_SCHEMA.TABLES table.

Running the following best source code, produces the results below:

USE SANDBOX
GO;

Select *
from INFORMATION_SCHEMA.TABLES
GO;

You can use this Database table with an IF THEN clause do determine how your sql query responds whether or not a database table exists.

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = N'member_id')
BEGIN
PRINT 'Good Luck, YES'
END

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = N'member_ids')
BEGIN
PRINT 'Good Luck, Yes'
END

ELSE

BEGIN
PRINT 'Sorry, No'
End

One of the more common uses We find for this when We need to create a database table in a script. We want to make sure a database table with same name doesn’t already exist, or my sql query will fail.

Therefor We write a sql query like the one below.

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = N'member_id')
BEGIN
drop table member_id
END

ELSE

BEGIN
CREATE TABLE member_id (
member_nm nvarchar(30) not null,
member_id nvarchar(8),
b_member_id nvarchar(8)
PRIMARY KEY(member_nm) );

INSERT INTO member_id
(member_nm, member_id)
VALUES
('radhika', 'A9825606245'),
('bhavika', 'A9696898955')

INSERT INTO member_id
(member_nm, b_member_id)
VALUES
('Krishna', 'B8958654254');
End

Sql Server check table exists before creating

USE [SANDBOX]
GO
IF OBJECT_ID('dbo.members', 'U') IS NOT NULL
BEGIN
PRINT 'members Table Exists in SQL Test DB'
END
ELSE
BEGIN
PRINT 'members Table Does not Exists'
END

Check if a Table exists or Not

USE [SANDBOX]
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = N'members')
BEGIN
PRINT 'members Table Exists in SQL Test DB'
END
ELSE
BEGIN
PRINT 'members Table Does not Exists'
END

Merhod : Check if a Table exists or Not

USE [SANDBOX]
GO
IF EXISTS(SELECT 1 FROM sys.Objects
WHERE Object_id = OBJECT_ID(N'dbo.members')
AND Type = N'M')
BEGIN
PRINT 'members Table Exists in SQL Test DB'
END
ELSE
BEGIN
PRINT 'members Table Does not Exists'
END

sql server check table exists

sql server check table exists

IF EXISTS
(SELECT object_id FROM sys.tables
WHERE name = 'bloggers'
AND SCHEMA_NAME(schema_id) = 'dbo')
PRINT 'The bloggers table exists'
ELSE
PRINT 'The bloggers table does not exist';

I hope you get an idea about sql check if table exists.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.