Check if table exists and if it doesn t exist create it in SQL Server 2008

Question

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

User · Answer

If I am not wrong  this should work       if not exists  Select 1 from tableName  create table

User · Answer

IF  EXISTS  SELECT                     FROM INFORMATION SCHEMA TABLES                   WHERE  TABLE NAME    d020915    BEGIN   declare  result int   set  result 1   select  result as result END

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-2008-r2