Check if table exists in SQL Server
I would like this to be the ultimate discussion on how to check if a table exists in SQL Server 2000/2005 using SQL Statements.
When you Google for the answer, you get so many different answers. Is there an official/backward and forward compatible way of doing it?
Here are two possible ways of doing it. Which one among the two is the standard/best way of doing it?
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='mytablename') SELECT 1 AS res ELSE SELECT 0 AS res;
IF OBJECT_ID (N'mytablename', N'U') IS NOT NULL SELECT 1 AS res ELSE SELECT 0 AS res;
MySQL provides the simple
SHOW TABLES LIKE '%tablename%';
statement. I am looking for something similar.
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
Read more... Read less...
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
We always use the
OBJECT_ID style for as long as I remember
IF OBJECT_ID('*objectName*', 'U') IS NOT NULL
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
Looking for a table on a different database:
if exists (select * from MyOtherDatabase.sys.tables where name = 'MyTable') print 'Exists'
Just wanted to mention one situation where it would probably be a little easier to use the
OBJECT_ID method. The
INFORMATION_SCHEMA views are objects under each database-
The information schema views are defined in a special schema named INFORMATION_SCHEMA. This schema is contained in each database.
Therefore all tables you access using
IF EXISTS (SELECT 1 FROM [database].INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='mytablename') SELECT 1 AS res ELSE SELECT 0 AS res;
will only reflect what is in
[database]. If you wanted to check if tables in another database exist, without dynamically changing the
[database] each time,
OBJECT_ID will let you do this out of the box. Ex-
IF OBJECT_ID (N'db1.schema.table1', N'U') IS NOT NULL SELECT 1 AS res ELSE SELECT 0 AS res;
works just as well as
IF OBJECT_ID (N'db2.schema.table1', N'U') IS NOT NULL SELECT 1 AS res ELSE SELECT 0 AS res;
SQL SERVER 2016 Edit:
Starting with 2016, Microsoft simplified the ability to check for non-existent objects prior to dropping, by adding the
if exists keywords to
drop statements. For example,
drop table if exists mytablename
will do the same thing as
INFORMATION_SCHEMA wrappers, in 1 line of code.