How to get a list of MySQL views?

Question

I am looking for a way to display all the views in a database.

First, I found and tried to answer in the MySQL forums :

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

Like never before, it returns an empty set. (I know they are there!)

They 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 is this not working?

Answer 1

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

MySQL query to find all views in a database

Answer 2

Here you can find all the views in each database of your instance:

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

Answer 3

  select * FROM information_schema.views\G; 

Answer 4

This will work.

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

Answer 5

Try moving this mysql.bak directory from /var/lib/mysql to say /root/ or something like that. It seems that mysql is finding this, and this may cause ERROR 1102 (42000): Incorrect database name 'mysql.bak' .

Answer 6

Your browsing error is probably related to a non-MySQL created directory created in the MySQL data directory. MySQL directly maps the database structure to the file system, databases are mapped to directories, and tables are files in these directories.

The name of a non-working database looks suspicious, as someone copied the mysql database to a backup at some point and left it in the MySQL data directory. This is not a problem if you are not trying to use the database for anything. Unfortunately, the information scheme scans all found databases and finds that it is not a real database and is upset.

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

Answer 7

Another way to find all View:

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

Answer 8

If you created a view in Mysql databases, you can just see it just like all your tables in your specific database.

write down:

 --mysql> SHOW TABLES; 

You will see a list of tables and views of your database.