> ## Documentation Index > Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt > Use this file to discover all available pages before exploring further. # SHOW TABLES The `SHOW TABLES` [statement](/docs/stable/sql-statements.md) lists the schema, table name, table type, owner, and estimated row count for the tables or [views](/docs/stable/views.md) in a schema or database. > [!NOTE] > > While a table or view is being [dropped](/docs/stable/drop-table.md), `SHOW TABLES` will list the object with a `(dropped)` suffix. ## Synopsis ![show\_tables syntax diagram](https://mintcdn.com/cockroachlabs/UZNSFbmU2EwEm6G5/images/sql-diagrams/common/show_tables.svg?fit=max\&auto=format\&n=UZNSFbmU2EwEm6G5\&q=85\&s=1c851788ee81c96cd2a538624f150f68) ## Required privileges The `CONNECT` [privilege](/docs/stable/security-reference/authorization.md#managing-privileges) on the database of the concerned table is required to list it with SHOW TABLES. ## Parameters | Parameter | Description | | - | - | | `database_name` | The name of the database for which to show tables. | | `schema_name` | The name of the schema for which to show tables. | When a `database_name` and `schema_name` are omitted, the tables of the [current schema](/docs/stable/sql-name-resolution.md#current-schema) in the [current database](/docs/stable/sql-name-resolution.md#current-database) are listed. `SHOW TABLES` will attempt to find a schema with the specified name first. If that fails, it will try to find a database with that name instead, and list the tables of its `public` schema. For more details, see [Name Resolution](/docs/stable/sql-name-resolution.md). ## Performance To optimize the performance of the `SHOW TABLES` statement, you can do the following: * Disable table row-count estimation by setting the `sql.show_tables.estimated_row_count.enabled` [cluster setting](/docs/stable/cluster-settings.md) to `false` before executing a `SHOW TABLES` statement. * Avoid running `SHOW TABLES` on databases with a large number of tables (e.g., more than 10,000 tables). ## Examples ### Setup The following examples use MovR, a fictional vehicle-sharing application, to demonstrate CockroachDB SQL statements. For more information about the MovR example application and dataset, see [MovR: A Global Vehicle-sharing App](/docs/stable/movr.md). To follow along, run [`cockroach demo`](/docs/stable/cockroach-demo) with the [`--nodes`](/docs/stable/cockroach-demo#flags) and [`--demo-locality`](/docs/stable/cockroach-demo#flags) flags. This command opens an interactive SQL shell to a temporary, multi-node in-memory cluster with the `movr` database preloaded and set as the [current database](/docs/stable/sql-name-resolution.md#current-database). ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} $ cockroach demo --nodes=6 --demo-locality=region=us-east,zone=us-east-a:region=us-east,zone=us-east-b:region=us-central,zone=us-central-a:region=us-central,zone=us-central-b:region=us-west,zone=us-west-a:region=us-west,zone=us-west-b ``` ### Show tables in the current database `SHOW TABLES` uses the [current schema](/docs/stable/sql-name-resolution.md#current-schema) `public` set by default in `search_path`: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW TABLES; ``` ``` schema_name | table_name | type | owner | estimated_row_count | locality --------------+----------------------------+-------+-------+---------------------+----------- public | promo_codes | table | demo | 0 | NULL public | rides | table | demo | 0 | NULL public | user_promo_codes | table | demo | 0 | NULL public | users | table | demo | 0 | NULL public | vehicle_location_histories | table | demo | 0 | NULL public | vehicles | table | demo | 0 | NULL (6 rows) ``` Alternatively, within the built-in SQL shell, you can use the `\dt` [shell command](/docs/stable/cockroach-sql.md#commands): ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > \dt ``` ``` schema_name | table_name | type | owner | estimated_row_count | locality --------------+----------------------------+-------+-------+---------------------+----------- public | promo_codes | table | demo | 0 | NULL public | rides | table | demo | 0 | NULL public | user_promo_codes | table | demo | 0 | NULL public | users | table | demo | 0 | NULL public | vehicle_location_histories | table | demo | 0 | NULL public | vehicles | table | demo | 0 | NULL (6 rows) ``` ### Show tables in a different schema You can show the tables in schemas other than the current schema. You can also show the schema by table: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW TABLES FROM movr.information_schema; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW TABLES FROM information_schema; ``` Because `movr` is the current database, these statements return the same output: ``` schema_name | table_name | type | owner | estimated_row_count | locality ---------------------+---------------------------------------+-------+-------+---------------------+----------- information_schema | administrable_role_authorizations | table | NULL | NULL | NULL information_schema | applicable_roles | table | NULL | NULL | NULL information_schema | attributes | table | NULL | NULL | NULL information_schema | character_sets | table | NULL | NULL | NULL ... (86 rows) ``` ### Show tables in a different database You can also show tables from a different database. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW TABLES FROM system.public; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW TABLES FROM system; ``` Because `public` is the current schema, these statements return the same output: ``` schema_name | table_name | type | owner | estimated_row_count | locality --------------+---------------------------------+-------+-------+---------------------+----------- public | comments | table | NULL | 0 | NULL public | database_role_settings | table | NULL | 0 | NULL public | descriptor | table | NULL | 0 | NULL public | eventlog | table | NULL | 0 | NULL ... (36 rows) ``` ### Show user-defined tables with comments You can use [`COMMENT ON`](/docs/stable/comment-on) to add comments on a table. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > COMMENT ON TABLE users IS 'This table contains information about users.'; ``` To view a table's comments: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW TABLES FROM movr WITH COMMENT; ``` ``` schema_name | table_name | type | owner | estimated_row_count | locality | comment --------------+----------------------------+-------+-------+---------------------+----------+----------------------------------------------- public | promo_codes | table | demo | 1000 | NULL | public | rides | table | demo | 500 | NULL | public | user_promo_codes | table | demo | 0 | NULL | public | users | table | demo | 50 | NULL | This table contains information about users. public | vehicle_location_histories | table | demo | 1000 | NULL | public | vehicles | table | demo | 15 | NULL | (6 rows) ``` You can also view comments on a table with [`SHOW CREATE`](/docs/stable/show-create): ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW CREATE TABLE users; ``` ``` table_name | create_statement -------------+--------------------------------------------------------------------------- users | CREATE TABLE users ( | id UUID NOT NULL, | city VARCHAR NOT NULL, | name VARCHAR NULL, | address VARCHAR NULL, | credit_card VARCHAR NULL, | CONSTRAINT users_pkey PRIMARY KEY (city ASC, id ASC) | ); | COMMENT ON TABLE users IS 'This table contains information about users.' (1 row) ``` For more information, see [`COMMENT ON`](/docs/stable/comment-on). ### Show virtual tables with comments The virtual tables in the `pg_catalog`, `information_schema`, and `crdb_internal` schemas contain useful comments, often with links to further documentation. To view virtual tables with comments and documentation links, use `SHOW TABLES FROM WITH COMMENT`: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW TABLES FROM information_schema WITH COMMENT; ``` ``` schema_name | table_name | type | owner | estimated_row_count | locality | comment ---------------------+---------------------------------------+-------+-------+---------------------+----------+------------------------------------------------------------------------------------------------------------------------------------- information_schema | administrable_role_authorizations | table | NULL | NULL | NULL | roles for which the current user has admin option | | | | | | https://www.cockroachlabs.com/docs/v26.3/information-schema.html#administrable_role_authorizations | | | | | | https://www.postgresql.org/docs/9.5/infoschema-administrable-role-authorizations.html information_schema | applicable_roles | table | NULL | NULL | NULL | roles available to the current user | | | | | | https://www.cockroachlabs.com/docs/v26.3/information-schema.html#applicable_roles | | | | | | https://www.postgresql.org/docs/9.5/infoschema-applicable-roles.html information_schema | attributes | table | NULL | NULL | NULL | attributes was created for compatibility and is currently unimplemented information_schema | character_sets | table | NULL | NULL | NULL | character sets available in the current database | | | | | | https://www.cockroachlabs.com/docs/v26.3/information-schema.html#character_sets | | | | | | https://www.postgresql.org/docs/9.5/infoschema-character-sets.html information_schema | check_constraint_routine_usage | table | NULL | NULL | NULL | check_constraint_routine_usage was created for compatibility and is currently unimplemented information_schema | check_constraints | table | NULL | NULL | NULL | check constraints | | | | | | https://www.cockroachlabs.com/docs/v26.3/information-schema.html#check_constraints | | | | | | https://www.postgresql.org/docs/9.5/infoschema-check-constraints.html ... (86 rows) ``` ### Show locality of tables For [multi-region](/docs/stable/multiregion-overview.md) tables, you can display the locality of each table using the `SHOW TABLES` command. 1. [Set the primary region](/docs/stable/alter-database.md#set-primary-region) on `movr` to `us-east`: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER DATABASE movr SET PRIMARY REGION "us-east"; ``` All tables will be [`REGIONAL BY TABLE`](/docs/stable/alter-table#set-the-table-locality-to-regional-by-table) in the primary region by default. 2. Configure the `users` table to be [`REGIONAL BY ROW`](/docs/stable/alter-table#set-the-table-locality-to-regional-by-row): ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE users SET LOCALITY REGIONAL BY ROW; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW TABLES; ``` ``` schema_name | table_name | type | owner | estimated_row_count | locality --------------+----------------------------+-------+-------+---------------------+-------------------------------------- public | promo_codes | table | demo | 1000 | REGIONAL BY TABLE IN PRIMARY REGION public | rides | table | demo | 500 | REGIONAL BY TABLE IN PRIMARY REGION public | user_promo_codes | table | demo | 0 | REGIONAL BY TABLE IN PRIMARY REGION public | users | table | demo | 50 | REGIONAL BY ROW public | vehicle_location_histories | table | demo | 1000 | REGIONAL BY TABLE IN PRIMARY REGION public | vehicles | table | demo | 15 | REGIONAL BY TABLE IN PRIMARY REGION (6 rows) ``` > [!NOTE] > > Locality information for tables is also available in the `locality` column within the [`crdb_internal.tables`](/docs/stable/crdb-internal) table. ## See also * [`SHOW DATABASES`](/docs/stable/show-databases) * [`SHOW SCHEMAS`](/docs/stable/show-schemas) * [`CREATE TABLE`](/docs/stable/create-table) * [`CREATE VIEW`](/docs/stable/create-view) * [`COMMENT ON`](/docs/stable/comment-on) * [Information Schema](/docs/stable/information-schema.md)