The best answers to the question “Check if table exists in SQL Server” in the category Dev.
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.
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
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
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
referred from: http://sqlhints.com/2014/04/13/how-to-check-if-a-table-exists-in-sql-server/
We always use the
OBJECT_ID style for as long as I remember
IF OBJECT_ID('*objectName*', 'U') IS NOT NULL