Check if table exists and if it doesn't exist, create it in SQL Server 2008
Something like this
IF NOT EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[YourTable]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[YourTable](
....
....
....
)
END
Just for contrast, I like using the object_id function as shown below. It's a bit easier to read, and you don't have to worry about sys.objects vs. sysobjects vs. sys.all_objects vs. sys.tables. Basic form:
IF object_id('MyTable') is not null
PRINT 'Present!'
ELSE
PRINT 'Not accounted for'
Of course this will show as "Present" if there is any object present with that name. If you want to check just tables, you'd need:
IF object_id('MyTable', 'U') is not null
PRINT 'Present!'
ELSE
PRINT 'Not accounted for'
It works for temp tables as well:
IF object_id('tempdb.dbo.#MyTable') is not null
PRINT 'Present!'
ELSE
PRINT 'Not accounted for'
Let us create a sample database with a table by the below script:
CREATE DATABASE Test
GO
USE Test
GO
CREATE TABLE dbo.tblTest (Id INT, Name NVARCHAR(50))
Approach 1: Using INFORMATION_SCHEMA.TABLES view
We can write a query like below to check if a tblTest Table exists in the current database.
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'tblTest')
BEGIN
PRINT 'Table Exists'
END
The above query checks the existence of the tblTest table across all the schemas in the current database. Instead of this if you want to check the existence of the Table in a specified Schema and the Specified Database then we can write the above query as below:
IF EXISTS (SELECT * FROM Test.INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = N'dbo' AND TABLE_NAME = N'tblTest')
BEGIN
PRINT 'Table Exists'
END
Pros of this Approach: INFORMATION_SCHEMA views are portable across different RDBMS systems, so porting to different RDBMS doesn’t require any change.
Approach 2: Using OBJECT_ID() function
We can use OBJECT_ID()
function like below to check if a tblTest Table exists in the current database.
IF OBJECT_ID(N'dbo.tblTest', N'U') IS NOT NULL
BEGIN
PRINT 'Table Exists'
END
Specifying the Database Name and Schema Name parts for the Table Name is optional. But specifying Database Name and Schema Name provides an option to check the existence of the table in the specified database and within a specified schema, instead of checking in the current database across all the schemas. The below query shows that even though the current database is MASTER database, we can check the existence of the tblTest
table in the dbo
schema in the Test
database.
USE MASTER
GO
IF OBJECT_ID(N'Test.dbo.tblTest', N'U') IS NOT NULL
BEGIN
PRINT 'Table Exists'
END
Pros: Easy to remember. One other notable point to mention about OBJECT_ID()
function is: it provides an option to check the existence of the Temporary Table which is created in the current connection context. All other Approaches checks the existence of the Temporary Table created across all the connections context instead of just the current connection context. Below query shows how to check the existence of a Temporary Table using OBJECT_ID()
function:
CREATE TABLE #TempTable(ID INT)
GO
IF OBJECT_ID(N'TempDB.dbo.#TempTable', N'U') IS NOT NULL
BEGIN
PRINT 'Table Exists'
END
GO
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.tblTest') 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'tblTest' AND Type = N'U')
BEGIN
PRINT 'Table Exists'
END
Sys.Tables
catalog view inherits the rows from the Sys.Objects
catalog view, Sys.objects
catalog view is referred to as base view where as sys.Tables
is referred to as derived view. Sys.Tables
will return the rows only for the Table objects whereas Sys.Object
view apart from returning the rows for table objects, it returns rows for the objects like: stored procedure, views etc.
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][1] 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'tblTest' AND xtype = N'U')
BEGIN
PRINT 'Table Exists'
END
Reference: http://sqlhints.com/2014/04/13/how-to-check-if-a-table-exists-in-sql-server/