248

CREATE TABLE IF NOT EXISTS works on mysql but fails with SQL Server 2008 R2. What is the equivalent syntax?

Samuel Liew
  • 76,741
  • 107
  • 159
  • 260
Sourabh
  • 4,545
  • 11
  • 39
  • 45
  • 1
    http://justcheckingonall.wordpress.com/2008/03/01/how-to-create-table-in-mssql-only-if-it-does-not-exist/ – Daniel Hilgarth Jun 29 '11 at 13:00
  • 12
    Actually this is not a duplicate of the marked question. This question is asking how to create if it does not exists. because we need a `GO` after create, we cannot put the create command inside `BEGIN` and `END` block, as the answer for other question suggests. – Bistro Oct 23 '14 at 07:00
  • 2
    @JNK, when you where marking this question as basic, your link might have been on top of the search results. however on this date, this question is on top of Google search. – Bistro Oct 23 '14 at 07:02
  • @Bistro it doesn't matter at all which question is higher ranked in google results. It matters which came first. This Q can still point to the original. – JNK Oct 23 '14 at 12:08
  • @Bistro And you definitely CAN do it inside a `BEGIN...END` block if you use dynamic sql. – JNK Oct 23 '14 at 12:09
  • IF not exists(select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME=YOUR TABLE NAME') begin //DO YOUR STUFF// end – Hadi Jun 03 '15 at 23:06
  • 4
    This was closed as a duplicate of a different question. The logic behind it being closed is like the [XY problem](https://meta.stackexchange.com/a/66378/206570) in reverse. You’re telling the asker that they should have asked `Y` when they’re actually asking `X` (whereas in the XY problem the asker asks `Y` when they’re trying to solve `X`). I.e., the asker did the right thing and the question is closed? *sad face* – binki Jun 12 '18 at 22:44
  • @binki If you think it's not a duplicate, you can nominate it for reopening. I totally agree with you; this question isn't identical. – Eamon Nerbonne Nov 28 '18 at 08:44
  • @EamonNerbonne I barely got [3000 rep](https://stackoverflow.com/help/privileges/close-questions) 14 hours ago. **Now** I can vote to reopen ;-). – binki Nov 28 '18 at 14:48
  • @binki That's hilarious! I had, of course, totally forgotten about the... uhm... odd... rep system stackoverflow has. – Eamon Nerbonne Nov 30 '18 at 10:53

1 Answers1

270
if not exists (select * from sysobjects where name='cars' and xtype='U')
    create table cars (
        Name varchar(64) not null
    )
go

The above will create a table called cars if the table does not already exist.

Neil Knight
  • 47,437
  • 25
  • 129
  • 188
  • 50
    Better yet - use the specific `sys.tables` catalog view instead of having to remember what obscure `xtype` a table is... – marc_s Jun 29 '11 at 13:40
  • 5
    @marc_s: Yes. Another good way :o) In fact, probably a better way. – Neil Knight Jun 29 '11 at 13:42
  • 78
    Since this is the top question for this topic in Google even though it has been closed: `if not exists (select * from sys.tables t join sys.schemas s on (t.schema_id = s.schema_id) where s.name = 'myschema' and t.name = 'cars') create table myschema.cars ( Name varchar(64) not null )` – bart Oct 22 '13 at 00:53
  • 112
    Coming back to this in 2017: `IF OBJECT_ID(N'dbo.Cars', N'U') IS NULL BEGIN CREATE TABLE dbo.Cars (Name varchar(64) not null); END;` – hastrb Oct 03 '17 at 14:16
  • 12
    @hastrb, i think you should convert your comment into an answer ;) – MaxU - stand with Ukraine Apr 20 '20 at 10:32
  • 7
    @hastrb Please make your answer as separate rather than having in comment. It really makes more sense and very elegant solution and after so many years pro devs are looking for this hidden solution. Thanks – sanpat Aug 28 '20 at 22:03
  • 4
    @sanpat the question is locked up, unfortunately. – hastrb Sep 14 '20 at 08:43