# SHOW OPEN TABLES

## Syntax

```sql
SHOW OPEN TABLES [FROM db_name]
    [LIKE 'pattern' | WHERE expr]
```

## Description

`SHOW OPEN TABLES` lists the non-`TEMPORARY` tables that are currently open in the table cache. See [table-cache.html](https://dev.mysql.com/doc/refman/5.1/en/table-cache.html).

The `FROM` and `LIKE` clauses may be used.

The `FROM` clause, if present, restricts the tables shown to those present in the`db_name` database.

The `LIKE` clause, if present on its own, indicates which table names to match. The `WHERE` and `LIKE` clauses can be given to select rows using more general conditions, as discussed in [Extended SHOW](/docs/server/reference/sql-statements/administrative-sql-statements/show/extended-show.md).

The following information is returned:

| Column       | Description                                                                         |
| ------------ | ----------------------------------------------------------------------------------- |
| Database     | Database name.                                                                      |
| Name         | Table name.                                                                         |
| In\_use      | Number of table instances being used.                                               |
| Name\_locked | 1 if the table is name-locked, e.g. if it is being dropped or renamed, otherwise 0. |

{% tabs %}
{% tab title="Current" %}
`LOCK TABLE... WRITE` acquires a strong MDL lock, and concurrent connections will wait on this MDL lock, so any subsequent `LOCK TABLE... WRITE` will not increment `In_use`.
{% endtab %}

{% tab title="< 5.5" %}
Before [MariaDB 5.5](/docs/release-notes/community-server/old-releases/5.5/changes-improvements-in-mariadb-5-5.md), each use of, for example, [LOCK TABLE ... WRITE](/docs/server/reference/sql-statements/transactions/lock-tables.md) would increment `In_use` for that table. With the implementation of the metadata locking improvements in MariaDB 5.5, `LOCK TABLE... WRITE` acquires a strong MDL lock, and concurrent connections will wait on this MDL lock, so any subsequent `LOCK TABLE... WRITE` will not increment `In_use`.
{% endtab %}
{% endtabs %}

## Example

```sql
SHOW OPEN TABLES;
+----------+---------------------------+--------+-------------+
| Database | Table                     | In_use | Name_locked |
+----------+---------------------------+--------+-------------+
...
| test     | xjson                     |      0 |           0 |
| test     | jauthor                   |      0 |           0 |
| test     | locks                     |      1 |           0 |
...
+----------+---------------------------+--------+-------------+
```

<sub>*This page is licensed: GPLv2, originally from*</sub> [<sub>*fill\_help\_tables.sql*</sub>](https://github.com/MariaDB/server/blob/main/scripts/fill_help_tables.sql)

{% @marketo/form formId="4316" %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-open-tables.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.