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. ...