How to get a list of MySQL views ?

Question

I'm looking for a way to list all views in a database.

Initially I found and tried an answer on the MySQL forums:

SELECT table_name
FROM information_schema.views
WHERE information_schema.views.table_schema LIKE 'view%';

How ever this doesn't work, returning an empty set. (I know they're in there!)

These also fail:

mysql> use information_schema;
Database changed
mysql> select * from views;
ERROR 1102 (42000): Incorrect database name 'mysql.bak'
mysql> select * from tables;
ERROR 1102 (42000): Incorrect database name 'mysql.bak'

Why isn't this working?

Answer 1

SELECT table_name FROM information_schema.views;

You don't need the where clause, you're already querying from views list table. Your query just tries to find table_schema's that start with 'view', but you don't need it.

Here's more on this subject

http://dev.mysql.com/doc/refman/5.0/en/views-table.html

UPDATE

your error doesn't concert to the query. The query is ok. The problem is in database name. Maybe the problem is in using dot ("."). Check links for more information. If it doesn't help, please, provide information on what you have tried and why it doesn't help.

http://www.bigresource.com/Tracker/Track-mysql-yOYU0qXM/

http://bytes.com/topic/mysql/answers/864050-mysql-4-invalid-database-name-howto-delete

Answer 2

The error your seeing is probably due to a non-MySQL created directory in MySQL's data directory. MySQL maps the database structure pretty directly onto the file system, databases are mapped to directories and tables are files in those directories.

The name of the non-working database looks suspiciously like someone has copied the mysql database directory to a backup at some point and left it in MySQL's data directory. This isn't a problem as long as you don't try and use the database for anything. Unfortunately the information schema scans all of the databases it finds and finds that this one isn't a real database and gets upset.

The solution is to find the mysql.bak directory on the hard disk and move it well away from MySQL.