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