TABLES
Applies to: 
Databricks SQL Databricks Runtime 10.4 LTS and above 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_PATH | STRING | Yes | No | Storage root URL for managed and external tables. |
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
SQL
> 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. ...