TABLES

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 10.4 LTS and above check marked yes Unity Catalog only

INFORMATION_SCHEMA.TABLES contains the object-level metadata for tables and views (relations) within the local catalog, or all catalogs visible to the workspace, if owned by the SYSTEM catalog.

The rows returned are limited to the relations the user is privileged to interact with.

Definition

The TABLES relation contains the following columns:

Name

Data type

Nullable

Standard

Description

TABLE_CATALOG

STRING

No

Yes

Catalog that contains the relation.

TABLE_SCHEMA

STRING

No

Yes

Schema that contains the relation.

TABLE_NAME

STRING

No

Yes

Name of the relation.

TABLE_TYPE

STRING

No

Yes

See Table types.

IS_INSERTABLE_INTO

STRING

No

Yes

'YES' if the relation can be inserted into, 'NO' otherwise.

COMMIT_ACTION

STRING

No

Yes

Always 'PRESERVE'. Reserved for future use.

TABLE_OWNER

STRING

No

No

User or group (principal) currently owning the relation.

COMMENT

STRING

Yes

No

An optional comment that describes the relation.

CREATED

TIMESTAMP

No

No

Timestamp when the relation was created.

CREATED_BY

STRING

No

No

Principal which created the relation.

LAST_ALTERED

TIMESTAMP

No

No

Timestamp when the relation definition was last altered in any way.

LAST_ALTERED_BY

STRING

No

No

Principal which last altered the relation.

DATA_SOURCE_FORMAT

STRING

No

No

Format of the data source such as PARQUET, or CSV.

STORAGE_SUB_DIRECTORY

STRING

Yes

No

Discontinued. Always NULL.

Constraints

The following constraints apply to the TABLES relation:

Class

Name

Column List

Description

Primary key

TABLES_PK

TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME

Unique identifier for the relation.

Foreign key

TABLES_SCHEMATA_FK

TABLE_CATALOG, TABLE_SCHEMA

References SCHEMATA.

Table types

The TABLE_TYPE column in the TABLES relation can have the following values:

  • VIEW: A virtual table that is defined by a query.

  • FOREIGN: A federated table.

  • MANAGED: A regular table for which the storage is managed by the catalog.

  • STREAMING_TABLE: A table that is used for streaming data.

  • MATERIALIZED_VIEW: A table that is a materialized view.

  • EXTERNAL: A table for which the storage is managed outside the catalog.

  • MANAGED_SHALLOW_CLONE: A table that is a shallow clone of a managed table.

  • EXTERNAL_SHALLOW_CLONE: A table that is a shallow clone of an external table.

Examples 

> SELECT *
    FROM information_schema.tables
    WHERE table_schema = 'information_schema'
      AND table_name = 'columns';
  table_catalog table_schema       table_name table_type is_insertable_into commit_action table_owner comment                                               ...
  ------------- ------------------ ---------- ---------- ------------------ ------------- ----------- ----------------------------------------------------- ...
  main          information_schema columns    VIEW       NO                 PRESERVE      System user Describes columns of tables and views in the catalog. ...