MySQL Forums
Forum List  »  Stored Procedures

Re: Does Table Exist
Posted by: Fernando Paiva
Date: June 11, 2020 11:25AM

|
|
|

I Like the solution that AWS has for it (as a SP in the SYS schema called "table_exists")

|
|
|


CREATE PROCEDURE sys.table_exists(IN in_db VARCHAR(64), IN in_table VARCHAR(64), OUT out_exists ENUM('', 'BASE TABLE', 'VIEW', 'TEMPORARY') )
SQL SECURITY INVOKER
COMMENT '
Description

Tests whether the table specified in in_db and in_table exists either as a regular
table, or as a temporary table. The returned value corresponds to the table that
will be used, so if there''s both a temporary and a permanent table with the given
name, then ''TEMPORARY'' will be returned.

Parameters

in_db (VARCHAR(64)):
The database name to check for the existance of the table in.

in_table (VARCHAR(64)):
The name of the table to check the existance of.

out_exists ENUM('''', ''BASE TABLE'', ''VIEW'', ''TEMPORARY''):
The return value: whether the table exists. The value is one of:
* '''' - the table does not exist neither as a base table, view, nor temporary table.
* ''BASE TABLE'' - the table name exists as a permanent base table table.
* ''VIEW'' - the table name exists as a view.
* ''TEMPORARY'' - the table name exists as a temporary table.

Example

mysql> CREATE DATABASE db1;
Query OK, 1 row affected (0.07 sec)

mysql> use db1;
Database changed
mysql> CREATE TABLE t1 (id INT PRIMARY KEY);
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TABLE t2 (id INT PRIMARY KEY);
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE view v_t1 AS SELECT * FROM t1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TEMPORARY TABLE t1 (id INT PRIMARY KEY);
Query OK, 0 rows affected (0.00 sec)

mysql> CALL sys.table_exists(''db1'', ''t1'', @exists); SELECT @exists;
Query OK, 0 rows affected (0.00 sec)

+------------+
| @exists |
+------------+
| TEMPORARY |
+------------+
1 row in set (0.00 sec)

mysql> CALL sys.table_exists(''db1'', ''t2'', @exists); SELECT @exists;
Query OK, 0 rows affected (0.00 sec)

+------------+
| @exists |
+------------+
| BASE TABLE |
+------------+
1 row in set (0.01 sec)

mysql> CALL sys.table_exists(''db1'', ''v_t1'', @exists); SELECT @exists;
Query OK, 0 rows affected (0.00 sec)

+---------+
| @exists |
+---------+
| VIEW |
+---------+
1 row in set (0.00 sec)

mysql> CALL sys.table_exists(''db1'', ''t3'', @exists); SELECT @exists;
Query OK, 0 rows affected (0.01 sec)

+---------+
| @exists |
+---------+
| |
+---------+
1 row in set (0.00 sec)
'
BEGIN
DECLARE v_error BOOLEAN DEFAULT FALSE;

DECLARE CONTINUE HANDLER FOR 1050
SET v_error = TRUE;

DECLARE CONTINUE HANDLER FOR 1146
SET v_error = TRUE;

SET out_exists = '';

IF (EXISTS(SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA = in_db AND TABLE_NAME = in_table)) THEN

SET @sys.tmp.table_exists.SQL = CONCAT('CREATE TEMPORARY TABLE `', in_db, '`.`', in_table, '` (id INT PRIMARY KEY)');

PREPARE stmt_create_table FROM @sys.tmp.table_exists.SQL;
EXECUTE stmt_create_table;
DEALLOCATE PREPARE stmt_create_table;

IF (v_error) THEN
SET out_exists = 'TEMPORARY';
ELSE
SET @sys.tmp.table_exists.SQL = CONCAT('DROP TEMPORARY TABLE `', in_db, '`.`', in_table, '`');
PREPARE stmt_drop_table FROM @sys.tmp.table_exists.SQL;
EXECUTE stmt_drop_table;
DEALLOCATE PREPARE stmt_drop_table;

SET out_exists = (SELECT TABLE_TYPE FROM information_schema.TABLES WHERE TABLE_SCHEMA = in_db AND TABLE_NAME = in_table);
END IF;
ELSE
SET @sys.tmp.table_exists.SQL = CONCAT('SELECT COUNT(*) FROM `', in_db, '`.`', in_table, '`');

PREPARE stmt_select FROM @sys.tmp.table_exists.SQL;
IF (NOT v_error) THEN
DEALLOCATE PREPARE stmt_select;
SET out_exists = 'TEMPORARY';
END IF;
END IF;
END

Options: ReplyQuote


Subject
Views
Written By
Posted
274
April 29, 2020 02:15PM
94
April 29, 2020 03:25PM
85
May 04, 2020 02:19AM
92
April 30, 2020 11:16AM
85
May 04, 2020 02:21AM
Re: Does Table Exist
78
June 11, 2020 11:25AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.