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.