List views in MySQL Database

Knowledge-base/Databases-queries/MySQL queries

June 19th, 2022

[wp_social_sharing social_options='facebook,twitter,linkedin,pinterest,reddit' twitter_username='softbuilder1' facebook_text='Share on Facebook' twitter_text='Share on Twitter' linkedin_text='Share on Linkedin' pinterest_text="Share on Pinterest" reddit_text="Share on Reddit" icon_order='f,t,l,p,x,r' show_icons='2' before_button_text='' text_position='' social_image='']

 

Say you need to list all views in your MySQL database. There are a few ways to get a list of all views in the database. This article will show you how to get the job done.

1. Using SQL Query

Let’s have a look at the first way to show all views in the MySQL database, using a SQL script:

SELECT table_schema, table_name 
FROM information_schema.views 
WHERE table_schema not in ('information_schema','mysql', 'performance_schema','sys') 
ORDER BY table_schema, table_name;

Sample results
The result of the given query will look like this:

list views in MySQL database

2. Using MySQL interactive shell command-line

To get a list of all the views in a MySQL database using MySQL shell command line you can use the following command: show full tables where table_type = ‘VIEW’;

The output of the show views command will be:

list views in MySQL database using MySQL shell

3. List views using ERBuilder Data Modeler

There’s another way to list all views in your database and that using ERBuilder Data Modeler which can list all views in the MySQL database.

For more details like the view's defining query, you just have to double-click on the view name and from there you can get all details of the view as you can edit it, add a new one, or delete the needless one.

A view’s defining query is shown in the screenshot below.

Start modeling your database with us today

Subscribe To Our Newsletter

Subscribe to our email newsletter today to receive updates of the latest news, tutorials and special offers!

Share on:

[wp_social_sharing social_options='facebook,twitter,linkedin,pinterest,reddit' twitter_username='softbuilder1' facebook_text='Share on Facebook' twitter_text='Share on Twitter' linkedin_text='Share on Linkedin' pinterest_text="Share on Pinterest" reddit_text="Share on Reddit" icon_order='f,t,l,p,x,r' show_icons='2' before_button_text='' text_position='' social_image='']

Choose a product

Choose a product and get 15 Day fully-functional free trial. No credit card required