MySQL query to find all views in a database - Geeks Worldwide


MySQL query to find all views in a database

You might find it useful to list all the views/tables in a particular database. I am going to show you three different methods to get the lists using GUI and command line tools. Replace database_name with the actual database name. Method1: To get the list of views in a particular database using MySQL command line interface (mysql), you'll run the SQL below SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'VIEW'; Similarly, you can run the following SQL to get the list of all tables. SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'BASE TABLE'; Method2: You can also list all the views using the SQL below. SELECT TABLE_NAME FROM information_schema.`TABLES` WHERE TABLE_TYPE LIKE 'VIEW' AND TABLE_SCHEMA LIKE 'database_name'; Similarly, you can run the following SQL to get the list of all tables. SELECT TABLE_NAME FROM information_schema.`TABLES` WHERE TABLE_TYPE LIKE 'BASE TABLE' AND TABLE_SCHEMA LIKE 'database_name'; Method3: You can also use MySQL administrator tool to find the list of all views/tables in a particular database Steps: Connect to the database server. On the left hand side pane (top section), click on Catalogs option. The list of schemas in the database server will show up in the bottom section on the left. Click on the database name that you want to select. The right hand pane should change with the list of all tables in the selected database. Click on Views tab at the top to list all the views in the database. Please feel free to use the comments form below if you have any questions or need more explanation on anything.	Tags mysql, sql, query, mysql-cli, mysql-administrator, Popular Searches linux, php, mysql, ubuntu, mysql mysql, tools, install mysql, gearman, source code, java more>>
Comments (write a comment): 0 comments so far. Be the first one to leave a comment on this article. Comment Name:* Email(not published):* Website (optional): Comment:* Notify me of followup comments via e-mail Similar Tutorials: How to download and install MySQLdb module for Python on Linux How to see table definition statements in MySQL MySQL Performance Optimization Part I SQL: Union and Union All opensource-db-tools - A set of open source tools for MySQL Tutorials in 'Database Management Systems > MySQL' (more): MySQL DATE_FORMAT function and ORDER BY MySQL Query Cache not necessarily a bad thing Download all Aspersa MySQL tools/scripts through a single command Download all maatkit scripts through a single command MySQL error while dropping databases Recent Tutorials: MySQL DATE_FORMAT function and ORDER BY MySQL Query Cache not necessarily a bad thing Download all Aspersa MySQL tools/scripts through a single command Download all maatkit scripts through a single command How to make class diagrams using Composition