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 ;