Introduction

In MySQL, views are stored as schema objects, so listing them is really a metadata query problem. The two most common approaches are querying INFORMATION_SCHEMA.VIEWS or using SHOW FULL TABLES, depending on whether you want portable metadata columns or a quick interactive result.

Use INFORMATION_SCHEMA.VIEWS

The most explicit method is to query the metadata schema directly.

1SELECT TABLE_SCHEMA, TABLE_NAME
2FROM INFORMATION_SCHEMA.VIEWS
3WHERE TABLE_SCHEMA = 'your_database'
4ORDER BY TABLE_NAME;

This returns one row per view in the specified schema. It is useful because INFORMATION_SCHEMA can also expose other metadata such as the view definition, definer, and security mode.

If you want the views for the currently selected database:

1SELECT TABLE_NAME
2FROM INFORMATION_SCHEMA.VIEWS
3WHERE TABLE_SCHEMA = DATABASE()
4ORDER BY TABLE_NAME;

That avoids hard-coding the schema name in scripts.

Use SHOW FULL TABLES for a Quick Interactive Listing

In the MySQL client, SHOW FULL TABLES is often the fastest ad hoc option.

SHOW FULL TABLES IN your_database WHERE TABLE_TYPE = 'VIEW';

This works because MySQL classifies both tables and views in the result, and the TABLE_TYPE column lets you filter for only views.

If you already selected the database with USE your_database;, you can omit the schema:

SHOW FULL TABLES WHERE TABLE_TYPE = 'VIEW';

For interactive work, this is usually more convenient than querying INFORMATION_SCHEMA by hand.

It is also easy to copy into a MySQL shell session when you are debugging a live schema and simply want a quick inventory of views without extra metadata columns.

Retrieve Definitions Too

Listing view names is often just the first step. If you need the SQL definition, you can ask MySQL directly:

SHOW CREATE VIEW your_view_name;

Or query metadata:

SELECT TABLE_NAME, VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = DATABASE();

That is especially useful for schema audits, migration tooling, or checking whether a view was created with the expected logic.

If the output is long, client settings may truncate or wrap it, so many teams use SHOW CREATE VIEW for one object at a time and INFORMATION_SCHEMA for bulk listings.

Permissions Still Apply

Metadata visibility depends on privileges. If you do not see views you expect, the issue may not be the query at all. The account may simply lack permission to inspect that schema or object metadata.

That is why an empty result set can mean either:

• there are no views in the schema
• your account cannot see them

When debugging, confirm the active database and the privileges of the current user.

In shared database environments, that distinction saves time. Developers often debug the query when the real issue is that they are connected to the wrong schema or using a low-privilege account.

Common Pitfalls

• Querying INFORMATION_SCHEMA.TABLES without filtering for TABLE_TYPE = 'VIEW'.
• Forgetting to limit the metadata query to the intended schema.
• Assuming an empty result means no views exist when permissions may be restricting visibility.
• Using SHOW TABLES, which does not distinguish clearly between tables and views the way SHOW FULL TABLES does.
• Forgetting that the current default database affects shortcut queries that rely on DATABASE() or USE schema_name.

Summary

• 'INFORMATION_SCHEMA.VIEWS is the standard metadata source for listing MySQL views.'
• 'SHOW FULL TABLES ... WHERE TABLE_TYPE = 'VIEW' is a convenient interactive shortcut.'
• Use DATABASE() when you want the current schema without hard-coding its name.
• 'SHOW CREATE VIEW is useful when you need the actual definition.'
• Check permissions if expected views do not appear.