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
SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'VIEW';