store in a @var a table's name from INFORMATION_SCHEMA.TABLES

mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%smi%';
+--------------+
| TABLE_NAME   | 
+--------------+ 
| smiley_files | 
| smileys      |
| smiley_files |
etc.
But:
mysql> SELECT @name:=(SELECT(TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME ='smileys'));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual etc.

How can I capture a table’s name from INFORMATION_SCHEMA.TABLES and store in a @var (or into another table)?

>Solution :

user define variables can only store one value so you would need somting like this

SELECT GROUP_CONCAT(TABLE_NAME) INTO @ltable 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME ='smileys';

As Table you would use

  CREATE TABLE MY_table as
SELECT TABLE_NAME  FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME ='smileys';

Leave a Reply