Check if table exists without using select from

Question

Is there a way to check if a table exists without selecting and checking values from it   That is  I know I can go SELECT testcol FROM testtable and check the count of fields returned  but it seems there must be a more direct   elegant way to do it

User · Answer

This has been my  go-to  EXISTS procedure that checks both temp and normal tables   This procedure works in MySQL version 5 6 and above   The  DEBUG parameter is optional   The default schema is assumed  but can be concatenated to the table in the  s statement   drop procedure if exists  prcDoesTableExist   delimiter   CREATE PROCEDURE  prcDoesTableExist  IN pin Table varchar 100   OUT pout TableExists BOOL  BEGIN     DECLARE  boolTableExists  TINYINT 1  DEFAULT 1      DECLARE CONTINUE HANDLER FOR 1243  SQLSTATE VALUE  42S02  SET  boolTableExists     0          SET  s   concat  SELECT null FROM     pin Table     LIMIT 0 INTO  resultNm        PREPARE stmt1 FROM  s      EXECUTE stmt1      DEALLOCATE PREPARE stmt1      set pout TableExists    boolTableExists   -- Set output variable     IF  DEBUG then         select IF  boolTableExists                CONCAT  TABLE     pin Table     exists     pout TableExists                CONCAT  TABLE     pin Table     does not exist     pout TableExists            as result      END IF  END   delimiter     Here is the example call statement with  debug on   set  DEBUG   true  call prcDoesTableExist  tempTable    tblExists   select  tblExists as   tblExists     The variable  tblExists returns a boolean

User · Answer

show tables like  table name   if this returns rows   0 the table exists

User · Answer

Just to add an extra way to do it  and depending on what you need it for you could use a handler for er no such table error 1146 like this   DELIMITER    CREATE PROCEDURE  insert in my table  in my var INT  BEGIN    -- Error number for table not found    DECLARE CONTINUE HANDLER FOR 1146    BEGIN       -- table doesn t exists  do something          CREATE TABLE my table n INT         INSERT INTO my table  n  values my var      END        -- table does exists  do something          INSERT INTO my table  n  values my var   END    DELIMITER

User · Answer

If you want to be correct  use INFORMATION SCHEMA   SELECT    FROM information schema tables WHERE table schema    yourdb       AND table name    testtable  LIMIT 1    Alternatively  you can use SHOW TABLES  SHOW TABLES LIKE  yourtable     If there is a row in the resultset  table exists

User · Answer

After reading all of the above  I prefer the following statement   SELECT EXISTS         SELECT   FROM information schema tables         WHERE table schema    db          AND table name    table       It indicates exactly what you want to do and it actually returns a  boolean

User · Answer

Rather than relying on errors  you can query INFORMATION SCHEMA TABLES to see if the table exists  If there s a record  it exists  If there s no record  it doesn t exist

User · Answer

Here is a table that is not a SELECT   FROM SHOW TABLES FROM  db  LIKE  tablename     zero rows   not exist  Got this from a database pro  here is what I was told  select 1 from  tablename     avoids a function call select   from INFORMATION SCHEMA tables where schema    db  and table    table     slow  Field names not accurate SHOW TABLES FROM  db  LIKE  tablename     zero rows   does not exist

User · Answer

There are several issues to note with the answers here   1  INFORMATION SCHEMA TABLES does not include TEMPORARY tables   2  Using any type of SHOW query  i e  SHOW TABLES LIKE  test table   will force the return of a resultset to the client  which is undesired behavior for checking if a table exists server-side  from within a stored procedure that also returns a resultset   3  As some users mentioned  you have to be careful with how you use SELECT 1 FROM test table LIMIT 1   If you do something like   SET  table exists   0  SET  table exists    SELECT 1 FROM test table LIMIT 1     You will not get the expected result if the table has zero rows   Below is a stored procedure that will work for all tables  even TEMPORARY    It can be used like   SET  test table    test table   SET  test db   NULL  SET  does table exist   NULL   CALL DoesTableExist  test table   test db   does table exist    SELECT  does table exist    The code          p table name is required     p database name is optional         if NULL is given for p database name  then it defaults to the currently selected database     p does table exist         The  variable to save the result to      This procedure attempts to         SELECT NULL FROM  p database name   p table name  LIMIT 0       If  SQLSTATE  42S02   is raised  then         SET p does table exist   0     Else         SET p does table exist   1      Info on SQLSTATE  42S02  at          https   dev mysql com doc refman 5 7 en server-error-reference html error er no such table     DELIMITER     DROP PROCEDURE IF EXISTS DoesTableExist     CREATE PROCEDURE         DoesTableExist       IN p table name VARCHAR 64       IN p database name VARCHAR 64       OUT p does table exist TINYINT 1  UNSIGNED   BEGIN        793441 is used in this procedure for ensuring that user variables have unique names         DECLARE EXIT HANDLER FOR SQLSTATE  42S02      BEGIN         SET p does table exist   0               END             IF p table name IS NULL THEN         SIGNAL SQLSTATE  45000  SET MESSAGE TEXT    DoesTableExist received NULL for p table name        END IF           redirect resultset to a dummy variable         SET  test select sql 793441   CONCAT           SET  dummy var 793441                    SELECT                    NULL                FROM                    IF                      p database name IS NULL                                              CONCAT                                                       REPLACE p database name                                                                                                                         REPLACE p table name                                                LIMIT 0                               PREPARE  sql statement FROM  test select sql 793441           SET  test select sql 793441   NULL           EXECUTE  sql statement           DEALLOCATE PREPARE  sql statement            SET p does table exist   1       END     DELIMITER

User · Answer

This compact method return 1 if exist 0 if not exist    set  ret   0   SELECT 1 INTO  ret FROM information schema TABLES           WHERE TABLE SCHEMA   DATABASE   AND TABLE NAME    my table    SELECT  ret    You can put in into a mysql function  DELIMITER    CREATE FUNCTION ExistTable   tableName varchar 255   RETURNS tinyint 4  SQL SECURITY INVOKER BEGIN   DECLARE  ret tinyint    SET  ret   0    SELECT     1 INTO  ret   FROM information schema TABLES   WHERE TABLE SCHEMA   DATABASE     AND TABLE NAME    tablename LIMIT 1    RETURN  ret  END    DELIMITER     and call it  Select ExistTable  my table      return 1 if exist 0 if not exist

User · Answer

You can do something like below               string strCheck    SHOW TABLES LIKE   tableName                 cmd   new MySqlCommand strCheck  connection               if  connection State    ConnectionState Closed                                connection Open                              cmd Prepare                var reader   cmd ExecuteReader                if  reader HasRows                                                           Console WriteLine  Table Exist                               else                                                          Console WriteLine  Table does not Exist

User · Answer

SELECT count    FROM information schema TABLES WHERE  TABLE SCHEMA    your db name   AND  TABLE NAME    name of table     if you get a non-zero count  the table exists

User · Answer

This modified solution from above does not require explicit knowledge of the current database  It is then more flexible   SELECT count    FROM information schema TABLES WHERE TABLE NAME    yourtable   AND TABLE SCHEMA in  SELECT DATABASE

User · Answer

You can query the INFORMATION SCHEMA tables system view   SELECT table name FROM information schema tables WHERE table schema    databasename  AND table name    testtable     If no rows returned  then the table doesn t exist

User · Answer

None of the options except SELECT doesn t allow database name as used in SELECT  so I wrote this   SELECT COUNT    AS cnt FROM information schema TABLES  WHERE CONCAT table schema     table name   db name table name

User · Answer

Expanding this answer  one could further write a function that returns TRUE FALSE based on whether or not a table exists   CREATE FUNCTION fn table exists dbName VARCHAR 255   tableName VARCHAR 255     RETURNS BOOLEAN   BEGIN     DECLARE totalTablesCount INT DEFAULT         SELECT COUNT          FROM information schema TABLES       WHERE  TABLE SCHEMA COLLATE utf8 general ci   dbName COLLATE utf8 general ci          AND  TABLE NAME COLLATE utf8 general ci   tableName COLLATE utf8 general ci             RETURN IF        totalTablesCount  gt  0        TRUE        FALSE        END     SELECT fn table exists  development    user

User · Answer

I use this in php   private static function ifTableExists string  database  string  table   bool                query   DB  select               SELECT                  IF  EXISTS                       SELECT   FROM information schema COLUMNS                         WHERE TABLE SCHEMA     database                          AND TABLE NAME     table                          LIMIT 1                   1  0                  AS if exists                      return  query 0 - gt if exists    1

User · Answer

A performance comparison     MySQL 5 0 77  on a db that has about 11 000 tables  Selecting a non-recently-used table so it s not cached  Averaged over 10 tries each    Note  done with different tables to avoid caching     322ms  show tables like  table201608    691ms  select 1 from table201608 limit 1   319ms  SELECT count    FROM information schema TABLES WHERE  TABLE SCHEMA    mydb   AND  TABLE NAME    table201608     Note if you re running this a lot -- like over many HTML requests in a short period -- the 2nd will be way faster since it ll be cached an average 200 ms or faster

[mysql] Check if table exists without using "select from"

Examples related to mysql

Examples related to sql