mysql – Search database name in information_schema database

mysql – Search database name in information_schema database – Database Administrators Stack Exchange

Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.

Anybody can ask a question

Anybody can answer

The best answers are voted up and rise to the top

Asked
today

Viewed
2 times

This is my sample data. I would like to find other database besides information_schema.

mysql> SELECT * FROM TABLES LIMIT 2;
+---------------+--------------------+----------------+-------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA       | TABLE_NAME     | TABLE_TYPE  | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------------+----------------+-------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------------+
| NULL          | information_schema | CHARACTER_SETS | SYSTEM VIEW | MEMORY |       0 | Fixed      |       NULL |            576 |           0 |        16661376 |            0 |         0 |           NULL | NULL        | NULL        | NULL       | utf8_general_ci |     NULL | max_rows=29127 |               | 
| NULL          | information_schema | COLLATIONS     | SYSTEM VIEW | MEMORY |       0 | Fixed      |       NULL |            423 |           0 |        16737264 |            0 |         0 |           NULL | NULL        | NULL        | NULL       | utf8_general_ci |     NULL | max_rows=39662 |               | 
+---------------+--------------------+----------------+-------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------------+
2 rows in set (0.06 sec)

mysql>

So, I use this syntax. What’s wrong in this and how do I fix it?

mysql> SELECT * FROM TABLES WHERE TABLE_SCHEMA NOT IN information_schema;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'information_schema' at line 1
mysql>