Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2007
    Posts
    22

    Unanswered: Check the existence of a table

    Hi;
    I made a lot of research but I couldnt find what I want. Is there any function in MySQL which detects is there any table called "x" or not? I wnat to detect it and if it doesnt exist a SQL sentence will create a table. How can I do that? (I think a stored procedure can help me but how can I detect the existence of a table?)

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select count(*)
      from information_schema.tables
     where table_schema = 'db_name'
       and table_name = 'x'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2007
    Posts
    22
    Thanks Im tring it...

  4. #4
    Join Date
    Jul 2007
    Posts
    22
    How can use it with SQL commands?
    I tried something like this:
    IF((select count(*)
    from information_schema.tables
    where table_schema = 'db_name'
    and table_name = 'x')=0,CREATE TABLE....)
    But it didnt work. I dont want to use a stored procedure. Is there any other way?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    other ways?

    1. run the query, look at the count, and if it's zero, run the CREATE

    2. embed the query in a language like php which has IF statements

    3. use a stored proc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    Use -
    Code:
    CREATE TABLE IF NOT EXISTS tbl_name ...

  7. #7
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Of course if you just want to find out if the table exists (i.e. not create it but produce a list of available tables?) then using the last option is not valid.

    If you have MySQL 5+ use the information_schema tables. Definitely the way to go...

  8. #8
    Join Date
    Jul 2007
    Posts
    22
    thanks very much...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •