CODEWITHSUNDEEP
mysqlmariadb
Charlie
Charlie

Reputation: 252

How to get a list of user-created databases in mysql

Im trying to get a list of all the user-created databases (not tables) from mysql, but I always get a list containing 'mysql', 'information_schema' and 'performance_schema'. Is it possible to filter these 3 out of the whole list?

I have tried with the query 'SHOW DATABASES' using the LIKE and NOT LIKE and also the wildcard '%', but not luck so far. I also tried with logical operators like AND and OR:

   SHOW DATABASES NOT LIKE 'information_schema' # doesn´t work
   SHOW DATABASES LIKE 'information_schema' # Works but outputs 1 record.
   SHOW DATABASES WHERE 'Database' NOT LIKE 'information_schema' # Works but doesn´t filter anything.

Do you guys have any idea how can I show only the user-created databases in mysql?

NOTE

In my view this is not a duplicate of When to use single quotes, double quotes, and backticks in MySQL, because the question is how to get a filtered list of user-created databases and NOT about the use of the backticks.

Best,

Upvotes: 0

Views: 926

Answers (2)

s_ramesh
s_ramesh

Reputation: 41

mysql> show databases where `database` not like 'information_schema';
+--------------------+
| Database           |
+--------------------+
| mysql              |
| performance_schema |
+--------------------+
2 rows in set (0.00 sec)



mysql> show databases where `database` not in('information_schema');
+--------------------+
| Database           |
+--------------------+
| mysql              |
| performance_schema |
+--------------------+
2 rows in set (0.00 sec)

Upvotes: 0

Tunji Oyeniran
Tunji Oyeniran

Reputation: 4394

Try using backtick for the Database Because this worked for me

SHOW DATABASES WHERE `Database` NOT LIKE 'information_schema'

Upvotes: 1

Related Questions