[mysql] How to get a list of MySQL views?

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?

If you created any view in Mysql databases then you can simply see it as you see your all tables in your particular database.

write:

--mysql> SHOW TABLES;

you will see list of tables and views of your database.

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.

This will work.

    USE INFORMATION_SCHEMA;
    SELECT TABLE_SCHEMA, TABLE_NAME
    FROM information_schema.tables
    WHERE TABLE_TYPE LIKE 'VIEW';

Another way to find all View:

SELECT DISTINCT table_name FROM information_schema.TABLES WHERE table_type = 'VIEW'

To complement about to get more info about a specific view

Even with the two valid answers

SHOW FULL TABLES IN your_db_name WHERE TABLE_TYPE LIKE 'VIEW';

SELECT TABLE_SCHEMA, TABLE_NAME 
FROM information_schema.TABLES 
WHERE TABLE_TYPE LIKE 'VIEW' AND TABLE_SCHEMA LIKE 'your_db_name';

You can apply the following (I think is better):

SELECT TABLE_SCHEMA, TABLE_NAME 
FROM information_schema.VIEWS 
WHERE TABLE_SCHEMA LIKE 'your_db_name';

is better work directly with information_schema.VIEWS (observe now is VIEWS and not TABLES anymore), thus you can retrieve more data, use DESC VIEWS for more details:

+----------------------+---------------------------------+------+-----+---------+-------+
| Field                | Type                            | Null | Key | Default | Extra |
+----------------------+---------------------------------+------+-----+---------+-------+
| TABLE_CATALOG        | varchar(64)                     | YES  |     | NULL    |       |
| TABLE_SCHEMA         | varchar(64)                     | YES  |     | NULL    |       |
| TABLE_NAME           | varchar(64)                     | YES  |     | NULL    |       |
| VIEW_DEFINITION      | longtext                        | YES  |     | NULL    |       |
| CHECK_OPTION         | enum('NONE','LOCAL','CASCADED') | YES  |     | NULL    |       |
| IS_UPDATABLE         | enum('NO','YES')                | YES  |     | NULL    |       |
| DEFINER              | varchar(93)                     | YES  |     | NULL    |       |
| SECURITY_TYPE        | varchar(7)                      | YES  |     | NULL    |       |
| CHARACTER_SET_CLIENT | varchar(64)                     | NO   |     | NULL    |       |
| COLLATION_CONNECTION | varchar(64)                     | NO   |     | NULL    |       |
+----------------------+---------------------------------+------+-----+---------+-------+

For example observe the VIEW_DEFINITION field, thus you can use in action:

SELECT TABLE_SCHEMA, TABLE_NAME, VIEW_DEFINITION 
FROM information_schema.VIEWS 
WHERE TABLE_SCHEMA LIKE 'your_db_name';

Of course you have more fields available for your consideration.

 select * FROM information_schema.views\G; 

Here's a way to find all the views in every database on your instance:

SELECT TABLE_SCHEMA, TABLE_NAME 
FROM information_schema.tables 
WHERE TABLE_TYPE LIKE 'VIEW';

Try moving that mysql.bak directory out of /var/lib/mysql to say /root/ or something. It seems like mysql is finding that and it may be causing that ERROR 1102 (42000): Incorrect database name 'mysql.bak' error.