MySQL Check if table exists error

Question

I'm trying to check if a table already exists, however I can't get this working.

IF EXISTS (SELECT 1 
    FROM sysobjects 
    WHERE xtype='u' AND name='tablename') 
        SELECT 'table already exists.' 
ELSE 
BEGIN
     CREATE TABLE Week_(
             id INT(10)AUTO_INCREMENT PRIMARY KEY (id),
             ...
             ...)
             END;

My error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF EXISTS (SELECT 1 FROM sysobjects WHERE xtype='u' AND name' at line 1

Can someone help me with this?

Thanks in advance

sysobjects, xtype='u'? Looks like you are using MS Sql Server. — Devart
– Devart, Commented Dec 10, 2012 at 11:12
aha, so that's why it didn't work ;) I'm using MySQL, found this code somewhere on the internet. — Resitive
– Resitive, Commented Dec 10, 2012 at 13:42

David Martin · Accepted Answer · 2012-12-10 10:59:38Z

19

In MySQL you can use the following syntax:

CREATE TABLE IF NOT EXISTS

http://dev.mysql.com/doc/refman/5.1/en/create-table.html

answered Dec 10, 2012 at 10:59

David Martin

12.4k2 gold badges65 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Saharsh Shah · Accepted Answer · 2012-12-10 11:04:11Z

7

Try this:

SELECT * 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'DBName' AND TABLE_NAME = 'TableName'

OR

CREATE TABLE IF NOT EXISTS 'TableName'

edited Dec 10, 2012 at 11:04

answered Dec 10, 2012 at 10:58

Saharsh Shah

29.1k9 gold badges53 silver badges85 bronze badges

1 Comment

Dijkgraaf Over a year ago

Note: the Select from information_schema.TABLES does not work in very early version of MySQL (e.g 4.1) as they didn't have those tables

Collectives™ on Stack Overflow

MySQL Check if table exists error

2 Answers 2

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Linked

Related