DESCRIBE TABLES EXTENDED¶

The DESCRIBE TABLES EXTENDED command lets you list all the tables in your database, including information about storage and deleted data. You can define the DESCRIBE TABLES EXTENDED command as either EXTERNAL or INTERNAL.

Note

DESCRIBE commands use CPU to increase usability.

Syntax¶

DESC[RIBE] TABLES EXTENDED [ DATABASE  <database_name> ] [ SCHEMA <schema_name> ] EXTERNAL | INTERNAL [LIKE 'table_name']

Parameters¶

Parameter	Type	Description
`DATABASE`	Identifier	The name of the database to search within
`SCHEMA`	Identifier	The name of the schema to search within
`EXTERNAL`, `INTERNAL`		You may define the `DESCRIBE TABLES` command to show information related to all tables, external tables, or internal tables. The default value is `ALL`
`LIKE`	STRING literal	String pattern to match

Output¶

Using INTERNAL with the DESCRIBE_TABLES_EXTENDED command generates the following output:

Parameter	Data Type	Description
`database_name`	`TEXT`	Displays the name of the database
`schema_name`	`TEXT`	Displays the name of the schema
`table_name`	`TEXT`	Displays the name of the table
`table_type`	`BOOLEAN`	`Internal` or `External`
`row_count`	`INTEGER`	Displays the number of rows in the table
`created_on`	`DATETIME`	Date and time of table creation
`Additional details`	`TEXT`
`number_of_chunks`	`INTEGER`	Displays the number of table chunks
`number_of_chunks_with_deleted_rows`	`INTEGER`	Displays the number of table chunks with deleted rows
`bytes(compressed)`	`INTEGER`
`bytes(uncompressed)`	`INTEGER`

Using EXTERNAL with the DESCRIBE_TABLES_EXTENDED command generates the following output:

Parameter	Data Type	Description
`database_name`	`TEXT`	Displays the name of the database
`table_id`	`INTEGER`	Displays the ID of the table
`schema_name`	`TEXT`	Displays the name of the schema
`table_name`	`TEXT`	Displays the name of the table
`format`	`BOOLEAN`	Indicates whether the table is formatted or not
`created`	`DATE`	Displays the table’s creation date and timestamp

Examples¶

DESCRIBE TABLES EXTENDED DATABASE master SCHEMA public INTERNAL;

database_name |schema_name |table_name |table_type |row_count |created_on          |Additional details |number_of_chunks |number_of_chunks_with_deleted_rows |bytes(compressed) |bytes(uncompressed)
--------------+------------+-----------+-----------+----------+--------------------+-------------------+-----------------+-----------------------------------+------------------+------------------
master        |public      |alex       |Internal   |1048576   |2023-08-21 10:54:40 |                   |21               |0                                  |294851            |15728640

DESCRIBE TABLES EXTENDED DATABASE master SCHEMA public EXTERNAL;

database_name|schema_name|table_name               |table_type|row_count|created_on         |Additional details                                                                                      |number_of_chunks|number_of_chunks_with_deleted_rows|bytes(compressed)|bytes(uncompressed)|
-------------+-----------+-------------------------+----------+---------+-------------------+--------------------------------------------------------------------------------------------------------+----------------+----------------------------------+-----------------+-------------------+
master       |public     |thirdpartydatacleaned    |External  |         |2023-08-22 11:38:53|Format: parquet, Path: gs://product_sqream/blue_demo/CleanedNValidatedData/3rdparty_cleaned.parquet     |                |                                  |                 |                   |
master       |public     |thirdpartydata           |External  |         |2023-08-22 11:39:42|Format: json, Path: gs://product_sqream/blue_demo/DataSources/thirdpartydata.json                       |                |                                  |                 |                   |
master       |public     |thirdpartydatatransformed|External  |         |2023-08-22 11:41:38|Format: parquet, Path: gs://product_sqream/blue_demo/TransformedData/3rdparty_transformed.parquet       |                |                                  |                 |                   |
master       |public     |nba                      |External  |         |2023-08-21 10:58:47|Format: parquet, Path: gs://blue_docs/nba.parquet                                                       |                |                                  |                 |                   |

DESCRIBE TABLES EXTENDED DATABASE master SCHEMA public EXTERNAL LIKE '%third%';

database_name|schema_name|table_name               |table_type|row_count|created_on         |Additional details                                                                                 |number_of_chunks|number_of_chunks_with_deleted_rows|bytes(compressed)|bytes(uncompressed)|
-------------+-----------+-------------------------+----------+---------+-------------------+---------------------------------------------------------------------------------------------------+----------------+----------------------------------+-----------------+-------------------+
master       |public     |thirdpartydatacleaned    |External  |         |2023-08-22 11:38:53|Format: parquet, Path: gs://product_sqream/blue_demo/CleanedNValidatedData/3rdparty_cleaned.parquet|                |                                  |                 |                   |
master       |public     |thirdpartydata           |External  |         |2023-08-22 11:39:42|Format: json, Path: gs://product_sqream/blue_demo/DataSources/thirdpartydata.json                  |                |                                  |                 |                   |
master       |public     |thirdpartydatatransformed|External  |         |2023-08-22 11:41:38|Format: parquet, Path: gs://product_sqream/blue_demo/TransformedData/3rdparty_transformed.parquet  |                |                                  |                 |                   |

Permissions¶

This command requires a CONNECT permission on the database level and a USAGE permission on the schema level.