# SHOW VARIABLES

Shows a list of variable bindings.

## Syntax

```

SHOW [CLUSTER] [LOCAL|GLOBAL] VARIABLES [EXTENDED]
[LIKE 'pattern' | WHERE expression]

```

## Arguments

* `pattern` - Can contain the `%` wildcard which represents zero or more characters. Can also contain the `_` wildcard which represents one character.
* `expression` - A SQL `WHERE` clause.
* See the [Permission Matrix](https://docs.singlestore.com/db/v9.1/reference/sql-reference/security-management-commands/permissions-matrix.md) for the required permission.

## Remarks

* `CLUSTER` - Displays the list of variables that can sync to all nodes. `_SYNC` and `CLUSTER` are synonymous. Use either of the options in the command.
* `LOCAL`- Displays the list of variable bindings in the current connection to the node. This includes the values of global variables that have been set. Values of session variables that can be set globally are not included, when these variables are set globally.
* `SESSION` and `LOCAL` are synonymous. Use either of the options in the command.
* `GLOBAL` - Displays the list of global variable bindings. Values of session variables that can be set globally are also included, when these variables are set globally.
* If the `_SYNC|CLUSTER` option is specified, `GLOBAL` variables are shown by default.
* If the `_SYNC|CLUSTER` option is not specified, `LOCAL` variables are shown by default.
* `EXTENDED` - Displays additional information about the variables in the output.

  * `Cluster_type` - If the variable is global, has the same value as `Variable_type`. If the variable is a session variable that can also be set globally, displays the [type of sync variable](https://docs.singlestore.com/db/v9.1/reference/configuration-reference/engine-variables/sync-variables.md). If the variable is not a sync variable, the value is `Local`.
  * `Variable_type` - Displays the [type of sync variable](https://docs.singlestore.com/db/v9.1/reference/configuration-reference/engine-variables/sync-variables.md). For non-sync variables and session variables that can also be set globally, the value is `Local`.
* This command can be run on any SingleStore node (see [Node Requirements for SingleStore Commands](https://docs.singlestore.com/db/v9.1/reference/sql-reference/cluster-management-commands.md)).

## Examples

## Using `SHOW VARIABLES` with a Wildcard

```sql

SHOW VARIABLES LIKE 'auto%';

```

```output

+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_attach              | ON    |
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
| auto_replicate           | OFF   |
| autocommit               | ON    |
+--------------------------+-------+

```

## Behavior of `SHOW LOCAL VARIABLES` When a Global Variable is Set

When a global variable is set, its value becomes effective in the current connection to the node. For this reason, after setting the global variable `connect_timeout` to `20`, this value is shown in the `SHOW LOCAL VARIABLES` output:

```sql

SET GLOBAL connect_timeout = 20;

SHOW LOCAL VARIABLES LIKE 'connect_timeout';

```

```output

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| connect_timeout | 20    |
+-----------------+-------+

```

## Behavior of `SHOW LOCAL VARIABLES` When a Session Variable that can be Set Globally is Set

When a session variable that can also be set globally is set globally, its value is not effective in the current connection to the node. For this reason, after setting the variable `enable_broadcast_left_join` to `OFF`, the value is not shown in the `SHOW LOCAL VARIABLES` output:

```sql

SET GLOBAL enable_broadcast_left_join = OFF;

SHOW LOCAL VARIABLES LIKE 'enable_broadcast_left_join';

```

```output

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| enable_broadcast_left_join | ON    |
+----------------------------+-------+

```

## Using the `EXTENDED` Clause

In this example, the value of `Variable_type` is `Local` because `character_set_server` is a session variable that can be set globally. The value of `Cluster_type` is not `Local` because `character_set_server` is a sync variable. The `In_Allow_List` column shows users with `SYSTEM_VARIABLE_ADMIN` privilege which variables they are allowed to set.

```sql


SHOW VARIABLES EXTENDED LIKE 'character_set_server';

```

```output

+----------------------+-------+---------------+----------------------------+---------------+
| Variable_name        | Value | Variable_type | Cluster_type               | In_Allow_List |
+----------------------+-------+---------------+----------------------------+---------------+
| character_set_server | utf8  | Local         | Cluster-wide for all nodes | Yes           |
+----------------------+-------+---------------+----------------------------+---------------+

SHOW VARIABLES EXTENDED LIKE 'memsql_version';

+----------------+-------+---------------+--------------+---------------+
| Variable_name  | Value | Variable_type | Cluster_type | In_Allow_List |
+----------------+-------+---------------+--------------+---------------+
| memsql_version | 7.8.0 | Local         | Local        | No            |
+----------------+-------+---------------+--------------+---------------+



```

## See Also

* [Engine variables overview](https://docs.singlestore.com/db/v9.1/reference/configuration-reference/engine-variables.md)

***

Modified at: January 11, 2023

Source: [/db/v9.1/reference/sql-reference/show-commands/show-variables/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/show-commands/show-variables/)

(An index of the documentation is available at /llms.txt)