TABLES

  • Article

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 meta data for tables and views (relations) within the local catalog or all catalogs 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 One of 'BASE TABLE', 'VIEW'.
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 Deprecated. 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.

Examples

> SELECT table_owner
    FROM information_schema.tables
    WHERE table_schema = 'information_schema'
      AND table_name = 'columns';