[sql] Check if table exists and if it doesn't exist, create it in SQL Server 2008

I am writing a Stored procedure in SQL Server 2008. I need to check if a table exists in the database. If it doesn't then I need to create it.

How do I do this?

IF (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE  TABLE_NAME = 'd020915'))
BEGIN
  declare @result int
  set @result=1
  select @result as result
END

If I am not wrong, this should work:

    if not exists (Select 1 from tableName)
create table ...

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/

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'

Declare @Username varchar(20)
Set @Username = 'Mike'

if not exists 
(Select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'tblEmp')

Begin
    Create table tblEmp (ID int primary key, Name varchar(50))
    Print (@Username + ' Table created successfully')
End

Else

Begin
    Print (@Username + ' : this Table Already exists in the database')
End

EDITED

You can look into sys.tables for checking existence desired table:

IF  NOT EXISTS (SELECT * FROM sys.tables
WHERE name = N'YourTable' AND type = 'U')

BEGIN
CREATE TABLE [SchemaName].[YourTable](
    ....
    ....
    ....
) 

END

Try the following statement to check for existence of a table in the database:

If not exists (select name from sysobjects where name = 'tablename')

You may create the table inside the if block.