--- title: "MySQL SHOW TABLES: A Detailed Guide | CoderPad" description: "The MySQL SHOW TABLES command is very appropriately named since it allows you to list tables on a MySQL database. Learn more about it in this guide." canonical: "https://coderpad.io/blog/development/mysql-show-tables-a-detailed-guide/" last_modified: "2026-04-15 16:20:58" --- [Back to blog](https://coderpad.io/blog/) # MySQL SHOW TABLES: A Detailed Guide February 6, 2023 5 Minute Read By Carlos Schults [Development](https://coderpad.io/blog/development/) The MySQL`SHOW tables`command is very appropriately named since it allows you to list tables on a MySQL database. By the end of the post, you’ll have learned: - The definition of the command - How you can use it - Why it’s useful in the first place, and - An alternative way to list the tables in a database without using the`show tables`command We’ll also cover how listing tables in MySQL compare with other RDBMSs (relational database management systems.) Let’s get started. ## Requirements There are only two prerequisites if you want to follow along with the examples I’ll give. First, I expect some basic MySQL and SQL knowledge. Also, you should have access to a MySQL local installation. A great way to do that is to use the [CoderPad MySQL Sandbox](https://coderpad.io/languages/mysql/) to quickly and easily get started writing SQL as it’ll be your MySQL client for this article. ## MySQL SHOW tables: what and why In MySQL, the`show tables`command is what you use to list the tables in a database. That’s pretty much it when it comes to defining the command. But what are the uses cases for it? After all, when using a MySQL GUI (graphical user interface) client, you can easily display the tables inside a database, typically in a tree. First, you won’t always have access to a GUI when interacting with MySQL. If the command line is all you have,`show tables`comes in handy. Also, keep in mind that this command is super flexible. When using it, you can leverage clauses such as`WHERE`and` [LIKE](https://coderpad.io/blog/development/mysql-like-operator-a-detailed-guide/)`to further filter the output. Finally, since this is a command, you can use it in scripts. So, there’s an opportunity to automate some tasks that simply wouldn’t be possible using a GUI. ## How do I view tables in MySQL? Let’s start with the most basic example. In the CoderPad MySQL sandbox, let’s create two tables: ``` CREATE TABLE students (     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,     name VARCHAR(250),     email VARCHAR(100)); CREATE TABLE teachers (     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,     name VARCHAR(250),     email VARCHAR(100));Code language: SQL (Structured Query Language) (sql) ``` Run`show tables`in the sandbox. You should see the tables being listed: ``` +--------------------+ | Tables_in_coderpad | +--------------------+ | students | | teachers | +--------------------+Code language: SQL (Structured Query Language) (sql) ``` ## MySQL SHOW tables: some practical examples You’ve just seen the most basic way you can use the`show tables`command. Now we’re ready to proceed to more interesting examples. ### How to tell tables and views apart Does the`SHOW tables`command also show views? Despite the name, yes. First, run the following command to create a view: ``` CREATE VIEW students_onlyname AS SELECT name FROM students;Code language: SQL (Structured Query Language) (sql) ``` Now, run`show tables`again, and you’ll see that the view is included. However, there’s no way to distinguish the view from the table. For that, you can use a different command: ``` SHOW full tables;Code language: SQL (Structured Query Language) (sql) ``` The command above returns the following output, which distinguishes between “base table” and “view”. ``` +--------------------+------------+ | Tables_in_coderpad | Table_type | +--------------------+------------+ | students | BASE TABLE | | students_onlyname | VIEW | | teachers | BASE TABLE | +--------------------+------------+Code language: SQL (Structured Query Language) (sql) ``` ### Filtering the results with a LIKE clause You often don’t want to get all of the existing tables, but a subset of them. You can use the`LIKE`clause to filter the results, in the same way, you’d do in a`SELECT`statement. For instance, running`show tables LIKE 'stu%'`returns the`student`table and the`student_onlyname`view, but not the`teachers`table. ### Filtering the results with a WHERE clause Similarly, you can filter your results using a`WHERE`clause. As you’ve already observed, when we ask MySQL to list its tables, the results themselves are presented in a tabular form, and the column which contains the table name is called “Tables_in_”. So, in our case, “Tables_in_school”. You can use that name to filter that column using the`WHERE`clause. Examples: ``` SHOW tables WHERE length(Tables_in_school) <= 8; SHOW tables WHERE Tables_in_school = 'students';Code language: SQL (Structured Query Language) (sql) ``` There’s nothing stopping you from using the`SHOW full tables`command as well, in which case you can also filter using the`Table_type`column: ``` SHOW full tables WHERE Table_type != 'VIEW'Code language: SQL (Structured Query Language) (sql) ``` ### Showing tables from a different database Up until now, all of the examples you’ve seen show how to list tables from the database you’re connected to. It’s also possible to list tables from a different database, and it’s extremely easy to do so. First, create a new database, running`CREATE database myblog;`. Then, select it by running`use myblog;`. Finally, you can list the tables from the first database by running`show tables from school;`. Keep in mind that, for that to work, you must have the necessary privileges on the other database. ## An additional way of listing tables in MySQL: The metadata tables The`SHOW tables`command is non-standard and unique to MySQL, which means it’s not transferable to other database engines. However, there’s another you to fetch the list of tables in MySQL, which is transferable to (at least some) other RDBMSs: the`INFORMATION_SCHEMA`views. > In relational databases, the information schema (information_schema) is an ANSI-standard set of read-only views that provide information about all of the tables, views, columns, and procedures in a database. – [Wikipedia](https://en.wikipedia.org/wiki/Information_schema). Here’s how to query the`tables`view to fetch the tables from the`school`database: ``` SELECT table_name, table_type FROM INFORMATION_SCHEMA.tables WHERE table_schema = 'school';Code language: SQL (Structured Query Language) (sql) ``` Keep in mind that the`INFORMATION_SCHEMA`views contain much more information than just the names of tables. There are views for columns, [indexes](https://coderpad.io/blog/development/how-to-use-indexes-to-increase-mysql-database-performance/), views, and all other artifacts inside a database. For instance, the following query displays data about the columns of the`students`table: ``` SELECT table_name, column_name, is_nullable, data_type, character_maximum_length FROM INFORMATION_SCHEMA.columns WHERE TABLE_SCHEMA = 'school' AND TABLE_NAME = 'students';Code language: SQL (Structured Query Language) (sql) ``` ## MySQL SHOW tables: add it to your toolkit MySQL`SHOW tables`is sort of a niche command. As you’ve seen, it’s unique to MySQL, since other database engines offer different ways to achieve the same results. Thus, it doesn’t qualify as a [SQL command everyone should know.](https://coderpad.io/blog/development/sql-functions-and-techniques-every-data-person-should-know/) However, that doesn’t mean it isn’t useful. If you’re accessing a MySQL database through the command line, this command should be a crucial part of your toolkit. With it, you can quickly and easily see and even filter the tables inside a given database. Even if you use a GUI-based MySQL client, learning how to use the filtering options when listing tables can help you better navigate complex [database schemas.](https://coderpad.io/blog/development/optimize-mysql-database-schema/) In the post, you’ve also learned that, despite being useful, the MySQL`SHOW tables`command is non-standard, which means you can use it on different database engines. A viable alternative is to use the`INFORMATION_SCHEMA`views: they’re an ANSI standard, implemented by several database vendors. Also, feel free to explore the CoderPad blog for more database-oriented posts, such as these: - [SQL Functions and Techniques Every Data Person Should Know](https://coderpad.io/blog/development/sql-functions-and-techniques-every-data-person-should-know/) - [Optimize MySQL Database Schema](https://coderpad.io/blog/development/optimize-mysql-database-schema/) - [MySQL LIKE Operator: A Detailed Guide](https://coderpad.io/blog/development/mysql-like-operator-a-detailed-guide/) Try out the`SHOW`command learned in this article in the MySQL sandbox below: Thanks for reading, until next time! This post was written by Carlos Schults. [Carlos](https://carlosschults.net) is a consultant and software engineer with experience in desktop, web, and mobile development. Though his primary language is C#, he has experience with a number of languages and platforms. His main interests include automated testing, version control, and code quality. ## Subscribe for more CoderPad insights ## Trending Articles Previous slide Next slide [Development ## What is Prompt Engineering? Introducing AI tools such as ChatGPT and Bard helps increase the efficiency of tasks such as data generation, code generation and completion, text summarization, etc. How were these AI tools trained to answer almost every question you ask them? The answer, prompt engineering, is discussed in this blog post. Read the post](https://coderpad.io/blog/development/what-is-prompt-engineering/) [Development ## A Guide To Using React’s useCallback() Hook Are you looking to optimize your React applications? Have you heard of the useCallback hook? useCallback is a powerful React hook that helps you optimize your application by preventing unnecessary re-renders. In this post, we'll dive into React's useCallback() hook, define referential equality and callback functions and how to tie it all together. Read the post](https://coderpad.io/blog/development/a-guide-to-using-reacts-usecallback-hook/) [Development ## MySQL vs. PostgreSQL: How Do They Compare? A database is a convenient tool that allows users to access information, maintain records, and apply commonly used operations, such as insertion, modification, deletion, and data organization. Learn about MySQL vs PostgreSQL, their features, advantages, and disadvantages. Finally, check out which one is the best choice for you. Read the post](https://coderpad.io/blog/development/mysql-vs-postgresql-how-do-they-compare/) [Development ## PostgreSQL LIKE Operator: A Detailed Guide When using databases, searching for values that match a given pattern is a familiar necessity. Learn how you can retrieve entries with queries including a particular string using Postgres LIKE operator. Read the post](https://coderpad.io/blog/development/postgresql-like-operator-a-detailed-guide/) [Development ## How To Use DataProvider In TestNG With Examples When it comes to testing there are many tools and techniques available. In this post, we'll discuss how to use DataProvider with TestNG. Read the post](https://coderpad.io/blog/development/how-to-use-dataprovider-in-testng-with-examples/) [Development ## How To Get Up And Running With Django Migrations: A Guide Django migrations are a way of handling the application of changes to a database schema. By following the steps outlined in this guide, you can easily set up and use migrations in your Django projects. Read the post](https://coderpad.io/blog/development/how-to-get-up-and-running-with-django-migrations-a-guide/) [Development ## Writing A Parameterized Test In JUnit With Examples A JUnit parameterized test allows you to decouple data from structure, generating cleaner and more reliable tests. Learn more in this guide. Read the post](https://coderpad.io/blog/development/writing-a-parameterized-test-in-junit-with-examples/) [Development ## How To Use Assert In JUnit With Examples The JUnit Assert class is what you use to write unit testing assertions in JUnit. Learn more about it in this guide, full of examples. Read the post](https://coderpad.io/blog/development/how-to-use-assert-in-junit-with-examples/) [Development ## A Simple Guide To TypeORM: What It Is And How To Use It Considering current languages have object-oriented programming features, most database management systems are based on the relational model, with tables instead of objects. For these reasons, mapping relational objects is very important in development. In this article, you will learn what TypeORM, one of the main tools used today, is. Read the post](https://coderpad.io/blog/development/a-simple-guide-to-typeorm-what-it-is-and-how-to-use-it/) [Development ## Using PreparedStatement in JDBC With Examples In this post, we'll see why and how to work with JDBC and PreparedStatement. We will also learn along with examples using code. Read the post](https://coderpad.io/blog/development/using-preparedstatement-in-jdbc-with-examples/) [Development ## React vs React Native: The Differences Explained React and React Native are similar in that they both allow web developers to build applications based on the unique concept of components. But the two frameworks do this in radically different ways. In this post, we cover the key differences between react and react native frameworks, implementation, and which is an appropriate choice. Read the post](https://coderpad.io/blog/development/react-vs-react-native-the-differences-explained/) [Development ## Logging in Django The most challenging part of fixing a problem is identifying it. Unfortunately, this statement is exceptionally true when it comes to debugging. Learn how to use the power of logging into your Django applications to improve your debug process in this article. Read the post](https://coderpad.io/blog/development/logging-in-django/) ## Hire with confidence. See why engineering teams trust CoderPad to evaluate developers with realistic, AI-ready hiring workflows. [Request a demo](https://coderpad.io/sales/)