Information schema
Applies to: 
Databricks SQL Databricks Runtime 10.4 LTS and above Unity Catalog only
The INFORMATION_SCHEMA is a SQL standard based schema, provided in every catalog created on Unity Catalog.
Within the information schema, you can find a set of views describing the objects known to the schema’s catalog that you are privileged to see.
The information schema of the SYSTEM catalog returns information about objects across all catalogs within the metastore. Information schema system tables do not contain metadata about hive_metastore objects.
The purpose of the information schema is to provide a SQL based, self describing API to the metadata.
Entity relationship diagram of the information schema
The following entity relationship (ER) diagram provides an overview of a subset of information schema views and how they relate to each other.
Information schema views
| Name | Description |
|---|---|
| CATALOG_PRIVILEGES | Lists principals that have privileges on the catalogs. |
| CATALOG_PROVIDER_SHARE_USAGE | Describes provider share mounted onto catalogs. |
| CATALOG_TAGS | Contains tags that have been applied to the catalogs. |
| CATALOGS | Describes catalogs. |
| CHECK_CONSTRAINTS | Reserved for future use. |
| COLUMN_MASKS | Describes column masks on table columns in the catalog. |
| COLUMN_TAGS | Contains column tagging metadata within a table. |
| COLUMNS | Describes columns of tables and views in the catalog. |
| CONNECTION_PRIVILEGES | Lists principals that have privileges on the foreign connections. |
| CONNECTIONS | Describes foreign connections. |
| CONSTRAINT_COLUMN_USAGE | Describes the constraints referencing columns in the catalog. |
| CONSTRAINT_TABLE_USAGE | Describes the constraints referencing tables in the catalog. |
| CREDENTIAL_PRIVILEGES | Lists principals that have privileges on the credentials. |
| CREDENTIALS | Describes credentials. |
| EXTERNAL_LOCATION_PRIVILEGES | Lists principals that have privileges on the external locations. |
| EXTERNAL_LOCATIONS | Describes external locations. |
| INFORMATION_SCHEMA_CATALOG_NAME | Returns the name of this information schema’s catalog. |
| KEY_COLUMN_USAGE | Lists the columns of the primary or foreign key constraints within the catalog. |
| METASTORE_PRIVILEGES | Lists principals that have privileges on the current metastore. |
| METASTORES | Describes the current metastore. |
| PARAMETERS | Describes parameters of routines (functions) in the catalog. |
| PROVIDERS | Describes providers. |
| RECIPIENT_ALLOWED_IP_RANGES | Lists allowed IP ranges for recipients. |
| RECIPIENT_TOKENS | Lists tokens for recipients. |
| RECIPIENTS | Describes recipients. |
| REFERENTIAL_CONSTRAINTS | Describes referential (foreign key) constraints defined in the catalog. |
| ROUTINE_COLUMNS | Describes result columns of table valued functions. |
| ROUTINE_PRIVILEGES | Lists principals that have privileges on the routines in the catalog. |
| ROUTINES | Describes routines (functions) in the catalog. |
| ROW_FILTERS | Describes row filters on tables in the catalog. |
| SCHEMA_PRIVILEGES | Lists principals that have privileges on the schemas in the catalog. |
| SCHEMA_TAGS | Contains schema tagging metadata within the schema. |
| SCHEMA_SHARE_USAGE | Describes the schemas referenced in shares. |
| SCHEMATA | Describes schemas within the catalog. |
| SHARE_RECIPIENT_PRIVILEGES | Describes the recipients granted access to shares. |
| SHARES | Describes shares. |
| STORAGE_CREDENTIAL_PRIVILEGES | [Deprecated] Lists principals that have privileges on the storage credentials. |
| STORAGE_CREDENTIALS | [Deprecated] Describes storage credentials. |
| TABLE_CONSTRAINTS | Describes metadata for all primary and foreign key constraints within the catalog. |
| TABLE_PRIVILEGES | Lists principals that have privileges on the tables and views in the catalog. |
| TABLE_SHARE_USAGE | Describes the tables referenced in shares. |
| TABLE_TAGS | Contains table tagging metadata within a table. |
| TABLES | Describes tables and views defined within the catalog. |
| VIEWS | Describes view specific information about the views in the catalog. |
| VOLUMES | Describes volumes defined in the catalog. |
| VOLUME_PRIVILEGES | Lists principals that have privileges on the volumes in the catalog. |
| VOLUME_TAGS | Contains volume tagging metadata applied to a volume. |
Notes
While identifiers are case-insensitive when referenced in SQL statements, they are stored in the information schema as STRING.
This implies that you must either search for them using the case in which the identifier is stored, or use functions such as ilike.
Examples
> SELECT table_name, column_name
FROM information_schema.columns
WHERE data_type = 'DOUBLE'
AND table_schema = 'information_schema';
The following are examples of workflows that use the system level information schema tables.
If you want to view all tables that have been created in the last 24 hours, your query could look like the following.
> SELECT table_name, table_owner, created_by, last_altered, last_altered_by, table_catalog
FROM system.information_schema.tables
WHERE datediff(now(), last_altered) < 1;
If you want to view how many tables you have in each schema, consider the following example.
> SELECT table_schema, count(table_name)
FROM system.information_schema.tables
WHERE table_schema = 'tpch'
GROUP BY table_schema
ORDER BY 2 DESC