Schema:: ACCOUNT_USAGE

TABLES View¶

This Account Usage view displays a row for each table and view in the account.

See also:: COLUMNS View , VIEWS View

Columns¶

Column Name	Data Type	Description
TABLE_ID	NUMBER	Internal, Snowflake-generated identifier for the table.
TABLE_NAME	TEXT	Name of the table.
TABLE_SCHEMA_ID	NUMBER	Internal, Snowflake-generated identifier of the schema for the table.
TABLE_SCHEMA	TEXT	Schema that the table belongs to.
TABLE_CATALOG_ID	NUMBER	Internal, Snowflake-generated identifier of the database for the table.
TABLE_CATALOG	TEXT	Database that the table belongs to.
TABLE_OWNER	TEXT	Name of the role that owns the table.
TABLE_TYPE	TEXT	Indicates the table type. Valid values are `BASE TABLE`, `TEMPORARY TABLE`, `EXTERNAL TABLE`, `EVENT TABLE`, `VIEW`, or `MATERIALIZED VIEW`.
IS_TRANSIENT	TEXT	Indicates whether the table is transient.
CLUSTERING_KEY	TEXT	Column(s) and/or expression(s) that comprise the clustering key for the table.
ROW_COUNT	NUMBER	Number of rows in the table.
BYTES	NUMBER	Number of bytes accessed by a scan of the table.
RETENTION_TIME	NUMBER	Number of days that historical data is retained for Time Travel.
SELF_REFERENCING_COLUMN_NAME	TEXT	Not applicable for Snowflake.
REFERENCE_GENERATION	TEXT	Not applicable for Snowflake.
USER_DEFINED_TYPE_CATALOG	TEXT	Not applicable for Snowflake.
USER_DEFINED_TYPE_SCHEMA	TEXT	Not applicable for Snowflake.
USER_DEFINED_TYPE_NAME	TEXT	Not applicable for Snowflake.
IS_INSERTABLE_INTO	TEXT	Not applicable for Snowflake.
IS_TYPED	TEXT	Not applicable for Snowflake.
COMMIT_ACTION	TEXT	Not applicable for Snowflake.
CREATED	TIMESTAMP_LTZ	Date and time when the table was created.
LAST_ALTERED	TIMESTAMP_LTZ	Date and time the object was last altered. See Usage Notes.
LAST_DDL	TIMESTAMP_LTZ	Timestamp of the last DDL operation performed on the table or view. All supported table/view DDL operations update this field: { CREATE \| ALTER \| DROP \| UNDROP } TABLE { CREATE \| ALTER \| DROP } VIEW All ALTER TABLE operations update this field, including setting or unsetting a table parameter (for example, COMMENT, DATA_RETENTION_TIME, etc.) and changes to table columns (ADD / MODIFY / RENAME / DROP). For more information, see the Usage Notes.
LAST_DDL_BY	TEXT	The current username for the user who executed the last DDL operation. If the user has been dropped, shows `DROPPED_USER(<id>)`. For dropped users, you can join the `<id>` with the USER_ID column in the USERS view.
DELETED	TIMESTAMP_LTZ	Date and time when the table was dropped.
AUTO_CLUSTERING_ON	TEXT	Status of Automatic Clustering for a table. For details, see Viewing the Automatic Clustering Status for a Table.
COMMENT	TEXT	Comment for the table.
OWNER_ROLE_TYPE	TEXT	The type of role that owns the object, either `ROLE` or `DATABASE_ROLE`. . If a Snowflake Native App owns the object, the value is `APPLICATION`. . Snowflake returns NULL if you delete the object because a deleted object does not have an owner role.
INSTANCE_ID	NUMBER	Internal/system-generated identifier for the instance which the object belongs to.
IS_ICEBERG	TEXT	Indicates whether the table is an Iceberg table. Valid values are `YES` or `NO`.
IS_DYNAMIC	TEXT	Indicates whether the table is a dynamic table. Valid values are `YES` or `NO`.

Usage Notes¶

Latency for the view may be up to 90 minutes.
The view does not recognize the MANAGE GRANTS privilege and consequently may show less information compared to a SHOW command executed by a user who holds the MANAGE GRANTS privilege.
Querying the SUM(BYTES) for a table does not represent the total storage usage, because the amount does not include Time Travel and Fail-safe usage.
The LAST_ALTERED column is updated when the following operations are performed on an object:
- DDL operations.
- DML operations (for tables only).
- Background maintenance operations on metadata performed by Snowflake.
For views and tables, use the LAST_DDL column for the last modification time for an object.
The value in the LAST_DDL column is updated as follows:
- When a table or view is created, the LAST_DDL timestamp is the same as the CREATED timestamp.
- When a table or view is dropped, the LAST_DDL timestamp is the same as the DELETED timestamp.
- Last DDL data is not available for operations that occurred before the columns were added. The new DDL fields contain null until a DDL operation is executed.
- For replicated databases, the LAST_DDL and LAST_DDL_BY fields are only updated for objects in the primary database. After failover, the LAST_DDL and LAST_DDL_BY fields are updated for DDL operations for the tables and views in the newly promoted primary database. These fields will remain unchanged for objects in the now secondary database.
- For objects in secondary databases that are newly created during a refresh operation, these fields are null.

Examples¶

Retrieve the total size (in bytes) of all active tables in all schemas in your account:

SELECT TABLE_SCHEMA,SUM(BYTES)
    FROM snowflake.account_usage.tables
    WHERE DELETED IS NULL
    GROUP BY TABLE_SCHEMA;

Copy