Below are four ways to list out the views in a MariaDB database using SQL or the command line.
The SHOW TABLES Command
The SHOW TABLES command lists the non-TEMPORARY tables, sequences and views in a given database. We can use the WHERE clause to narrow it to just views.
We can also use the FULL modifier to return a second column that displays the type:
SHOW FULL TABLES
WHERE Table_Type LIKE 'VIEW';Result:
+--------------------+------------+ | Tables_in_pethouse | Table_type | +--------------------+------------+ | vownercount | VIEW | | vpetcount | VIEW | | vpetsowners | VIEW | | vpetstypes | VIEW | | vpettypecount | VIEW | +--------------------+------------+
Omitting the WHERE clause returns all types:
SHOW FULL TABLES;Result:
+--------------------+------------+ | Tables_in_pethouse | Table_type | +--------------------+------------+ | Owners | BASE TABLE | | PetTypes | BASE TABLE | | Pets | BASE TABLE | | vownercount | VIEW | | vpetcount | VIEW | | vpetsowners | VIEW | | vpetstypes | VIEW | | vpettypecount | VIEW | +--------------------+------------+
The SHOW TABLE STATUS Command
The SHOW TABLE STATUS command is similar to the SHOW TABLES command but provides more extensive information about each (non-TEMPORARY) table.
Example:
SHOW TABLE STATUS;Result:
+---------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+------------------+-----------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | Max_index_length | Temporary | +---------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+------------------+-----------+ | Owners | InnoDB | 10 | Dynamic | 6 | 2730 | 16384 | 0 | 0 | 0 | NULL | 2021-03-30 09:10:36 | NULL | NULL | utf8mb4_general_ci | NULL | | | 0 | N | | PetTypes | InnoDB | 10 | Dynamic | 4 | 4096 | 16384 | 0 | 0 | 0 | NULL | 2021-03-30 09:10:36 | NULL | NULL | utf8mb4_general_ci | NULL | | | 0 | N | | Pets | InnoDB | 10 | Dynamic | 8 | 2048 | 16384 | 0 | 32768 | 0 | NULL | 2021-04-01 15:42:43 | NULL | NULL | utf8mb4_general_ci | NULL | | | 0 | N | | vownercount | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | VIEW | NULL | NULL | | vpetcount | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | VIEW | NULL | NULL | | vpetsowners | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | VIEW | NULL | NULL | | vpetstypes | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | VIEW | NULL | NULL | | vpettypecount | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | VIEW | NULL | NULL | +---------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+------------------+-----------+
It also accepts a WHERE and LIKE clause in case you want to narrow down the results.
The information_schema.TABLES Table
We can also query the information_schema.TABLES table:
SELECT
TABLE_SCHEMA,
TABLE_NAME,
TABLE_TYPE
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA LIKE 'pethouse' AND TABLE_TYPE LIKE 'VIEW';Result:
+--------------+---------------+------------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | +--------------+---------------+------------+ | PetHouse | vpettypecount | VIEW | | PetHouse | vpetstypes | VIEW | | PetHouse | vownercount | VIEW | | PetHouse | vpetcount | VIEW | | PetHouse | vpetsowners | VIEW | +--------------+---------------+------------+
Querying this without filtering the results by TABLE_SCHEMA returns views from all databases. Similarly, querying it without filtering by TABLE_TYPE returns all table types.
The mariadb-show Client
Another way to do it is with the mariadb-show utility.
To use this option, open a command line prompt/terminal window and run the following (replacing pethouse with the database you’re interested in):
mariadb-show pethouse;Result:
+---------------+ | Tables | +---------------+ | Owners | | PetTypes | | Pets | | vownercount | | vpetcount | | vpetsowners | | vpetstypes | | vpettypecount | +---------------+
This returns views and tables.
The output displays only the names of those databases, tables, or columns for which you have some privileges.
If no database is given then all matching databases are shown. If no table is given, then all matching tables in database are shown. If no column is given, then all matching columns and column types in table are shown.
The client can also be run as mysqlshow:
mysqlshow pethouse;This utility accepts quite a few options, such as --user (so that you can pass the username), --password (so that you can pass the password), etc.
See MariaDB’s documentation for a full list of options.