List Views in SQL: MySQL, MariaDB, PostgreSQL & More

A view is a stored query, but each database exposes views through different commands or catalog tables. The examples below list ordinary views; materialized views are separate objects in PostgreSQL and Oracle.

Quick reference

MySQL and MariaDB

Use SHOW FULL TABLES to return a table-type column, then filter it to views:

SHOW FULL TABLES FROM app_db
WHERE Table_type = 'VIEW';

MySQL labels ordinary views as VIEW and Information Schema views as SYSTEM VIEW; MariaDB can also return SEQUENCE as a separate type. The command only shows objects visible to the current account. See MySQL’s SHOW TABLES reference and MariaDB’s SHOW TABLES reference.

PostgreSQL

In psql, use \dv with a schema pattern to list views in that schema:

\dv public.*

To query view names and schemas with SQL, use pg_catalog.pg_views:

SELECT schemaname, viewname
FROM pg_catalog.pg_views
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, viewname;

These commands list ordinary views. Use \dm or pg_catalog.pg_matviews for materialized views. See PostgreSQL’s psql meta-command reference and pg_views catalog view.

SQL Server

Query sys.views and join sys.schemas when you need each view’s schema name:

SELECT s.name AS schema_name, v.name AS view_name
FROM sys.views AS v
JOIN sys.schemas AS s
    ON s.schema_id = v.schema_id
WHERE v.is_ms_shipped = 0
ORDER BY s.name, v.name;

This returns user-created views visible in the current database and excludes Microsoft-shipped objects. See Microsoft’s sys.views and metadata visibility references.

SQLite

Query the schema table and filter the object type:

SELECT name
FROM main.sqlite_schema
WHERE type = 'view'
ORDER BY name;

Replace main with temp or an attached database name, such as archive.sqlite_schema, to inspect another schema on the current connection. See SQLite’s sqlite_schema reference.

Oracle

Use USER_VIEWS to list views owned by the current user:

SELECT view_name
FROM user_views
ORDER BY view_name;

Use ALL_VIEWS when you need views accessible to the current user across owners; it includes an OWNER column. DBA_VIEWS describes all views and requires administrative privileges. Materialized views are listed separately, for example in USER_MVIEWS. See Oracle’s USER_VIEWS and ALL_VIEWS references.

To list tables as well as views, see List Tables in SQL. For other metadata tasks, browse the cross-database SQL comparison guides.