MySQL - SHOW OPEN TABLES Statement

Table of content

MySQL SHOW OPEN TABLES Statement
FROM or IN clause
The LIKE clause
The WHERE clause

Quiz

SHOW OPEN TABLES Statement

The CREATE TABLE statement is used to create tables in MYSQL database. Here, you need to specify the name of the table and, definition (name and datatype) of each column.

The SHOW OPEN TABLES statement displays the lists the non-TEMPORARY tables which are currently open in the table cache. In addition to names of the tables this statement also provides the name of the database the table is in and binary values (0 or 1) specifying whether the table is in use and if it is locked.

Syntax

Following is the syntax of the SHOW OPEN TABLES Statement −

SHOW OPEN TABLES
   [{FROM | IN} db_name]
   [LIKE 'pattern' | WHERE expr]

Example

Following statement displays the information about the tables that are currently open in the table cache −

SHOW OPEN TABLES\G;

Output

The above query produces the following output −

************* 1. row *************
   Database: mysql
      Table: check_constraints
     In_use: 0
Name_locked: 0
************* 2. row *************
   Database: mysql
      Table: column_type_elements
     In_use: 0
Name_locked: 0
************* 3. row *************
   Database: mysql
      Table: slave_master_info
     In_use: 0
Name_locked: 0
************* 4. row *************
   Database: mysql
      Table: foreign_keys
     In_use: 0
Name_locked: 0
************* 5. row *************
   Database: mysql
      Table: columns
     In_use: 0
Name_locked: 0
************* 6. row *************
   Database: mysql
      Table: foreign_key_column_usage
     In_use: 0
Name_locked: 0
************* 7. row *************
   Database: mysql
      Table: server_cost
     In_use: 0
Name_locked: 0
************* 8. row *************
   Database: mysql
      Table: index_column_usage
     In_use: 0
Name_locked: 0
************* 9. row *************
   Database: mysql
      Table: view_table_usage
     In_use: 0
Name_locked: 0
************* 10. row *************
   Database: mysql
      Table: index_partitions
     In_use: 0
Name_locked: 0
************* 11. row *************
   Database: mysql
      Table: indexes
     In_use: 0
Name_locked: 0
************* 12. row *************
   Database: mysql
      Table: schemata
     In_use: 0
Name_locked: 0
************* 13. row *************
   Database: mysql
      Table: collations
     In_use: 0
Name_locked: 0
************* 14. row *************
   Database: mysql
      Table: table_partition_values
     In_use: 0
Name_locked: 0
************* 15. row *************
   Database: mysql
      Table: table_partitions
     In_use: 0
Name_locked: 0
************* 16. row *************
   Database: mysql
      Table: tables
     In_use: 0
Name_locked: 0
************* 17. row *************
   Database: mysql
      Table: role_edges
     In_use: 0
Name_locked: 0
************* 18. row *************
   Database: mysql
      Table: triggers
     In_use: 0
Name_locked: 0
************* 19. row *************
   Database: test
      Table: contact
     In_use: 0
Name_locked: 0
************* 20. row *************
   Database: mysql
      Table: column_statistics
     In_use: 0
Name_locked: 0
........................
........................
........................

FROM or IN clause

You can retrieve information about the non-TEMPORARY tables that are currently open in the table cache from a specific database using the FROM clause.

SHOW OPEN TABLES FROM performance_schema;

Output

Following is the output of the above query −

Database	Table	In_use	Name_locked
performance_schema	events_waits_history_long	0	0
performance_schema	session_status	0	0
performance_schema	events_stages_history_long	0	0
performance_schema	events_statements_current	0	0
performance_schema	session_variables	0	0
performance_schema	threads	0	0

You can also use the IN clause instead of FROM as −

SHOW OPEN TABLES IN performance_schema;

Output

After executing the above query, it will produce the following output −

Database	Table	In_use	Name_locked
performance_schema	events_waits_history_long	0	0
performance_schema	session_status	0	0
performance_schema	events_stages_history_long	0	0
performance_schema	events_statements_current	0	0
performance_schema	session_variables	0	0
performance_schema	threads	0	0

The LIKE clause

Using the LIKE clause, you can specify a pattern to retrieve information about specific tables that are currently open in the table cache. Following query retrieves description about the tables with name starting with the word "information".

SHOW OPEN TABLES LIKE 'time%';

Output

Once the query is executed, it will produce the output shown below −

Database	Table	In_use	Name_locked
mysql	time_zone_transition	0	0
mysql	time_zone_leap_second	0	0
mysql	time_zone_name	0	0
mysql	time_zone	0	0
mysql	time_zone_transition_type	0	0

The WHERE clause

You can use the WHERE clause of the SHOW OPEN TABLES statements to retrieve description of the variables which match the specified condition.

Example

Assume we have created a table that contains the sales details as shown below −

CREATE TABLE SalesDetails (
   ID INT,
   ProductName VARCHAR(255),
   CustomerName VARCHAR(255),
   DispatchDate date,
   DeliveryTime time,
   Price INT,
   Location VARCHAR(255),
);

Now create another table with the contact details of the customer create a table as −

CREATE TABLE CustContactDetails (
   ID INT,
   Name VARCHAR(255),
   Age INT,
   Phone BIGINT,
   Address VARCHAR(255),
   Email VARCHAR(50)
);

Following query displays the Open tables that are current in use −

SHOW OPEN TABLES WHERE In_use=1;

Output

The above query generates the following output −

Database	Table	In_use	Name_locked
demo	custcontactdetails	1	0
demo	salesdetails	1	0

Print Page