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 gt  use information schema  Database changed mysql gt  select   from views  ERROR 1102  42000   Incorrect database name  mysql bak  mysql gt  select   from tables  ERROR 1102  42000   Incorrect database name  mysql bak    Why isn t this working

User · Answer

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.

User · Answer

SHOW FULL TABLES IN database name WHERE TABLE TYPE LIKE  VIEW     MySQL query to find all views in a database

User · Answer

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

User · Answer

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

User · Answer

select   FROM information schema views G

User · Answer

Another way to find all View   SELECT DISTINCT table name FROM information schema TABLES WHERE table type    VIEW

User · Answer

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

User · Answer

This will work       USE INFORMATION SCHEMA      SELECT TABLE SCHEMA  TABLE NAME     FROM information schema tables     WHERE TABLE TYPE LIKE  VIEW

User · Answer

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

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

Examples related to mysql