Check if MySQL table exists without using "select from" syntax?

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.

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.

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.

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.

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.

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'.

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

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.

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());

show tables like 'table_name'

if this returns rows > 0 the table exists

If reading this after 2019, note that MySQL 5.7 added a table_exists procedure that will determine if a table exists, including TEMPORARY TABLES.

Usage: Sets @exist to one of '', 'BASE TABLE', 'VIEW', 'TEMPORARY'

CALL sys.table_exists('db1', 't3', @exists);

Reference:

https://dev.mysql.com/doc/refman/5.7/en/sys-table-exists.html

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 ;

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!");
            }

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 > 0,
      TRUE,
      FALSE
    );
END
;


SELECT fn_table_exists('development', 'user');

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.

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]->if_exists == 1;
	}

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 ;

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.

Before creating a TABLE, it is always advisable to check whether the table exists in SQL Server database or not.

USE [DB_NAME]
GO
IF OBJECT_ID('table_name', 'U') IS NOT NULL
BEGIN
PRINT 'Table exists.'
END
ELSE
BEGIN
PRINT 'Table does not exist.'
END

Alternatively Using the sys.Objects to check whether a table exists in SQL Server or not.

USE [DB_NAME]
GO
IF EXISTS(SELECT 1 FROM sys.Objects
WHERE Object_id = OBJECT_ID(N'table_name')
AND Type = N'U')
BEGIN
PRINT 'Table exists.'
END
ELSE
BEGIN
PRINT 'Table does not exist.'
END

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";