Skip to main content

Information schema

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 10.4 LTS and above check marked yes 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.

ER diagram of information schema

Information schema views

NameDescription
CATALOG_PRIVILEGESLists principals that have privileges on the catalogs.
CATALOG_PROVIDER_SHARE_USAGEDescribes provider share mounted onto catalogs.
CATALOG_TAGSContains tags that have been applied to the catalogs.
CATALOGSDescribes catalogs.
CHECK_CONSTRAINTSReserved for future use.
COLUMN_MASKSDescribes column masks on table columns in the catalog.
COLUMN_TAGSContains column tagging metadata within a table.
COLUMNSDescribes columns of tables and views in the catalog.
CONNECTION_PRIVILEGESLists principals that have privileges on the foreign connections.
CONNECTIONSDescribes foreign connections.
CONSTRAINT_COLUMN_USAGEDescribes the constraints referencing columns in the catalog.
CONSTRAINT_TABLE_USAGEDescribes the constraints referencing tables in the catalog.
CREDENTIAL_PRIVILEGESLists principals that have privileges on the credentials.
CREDENTIALSDescribes credentials.
EXTERNAL_LOCATION_PRIVILEGESLists principals that have privileges on the external locations.
EXTERNAL_LOCATIONSDescribes external locations.
INFORMATION_SCHEMA_CATALOG_NAMEReturns the name of this information schema’s catalog.
KEY_COLUMN_USAGELists the columns of the primary or foreign key constraints within the catalog.
METASTORE_PRIVILEGESLists principals that have privileges on the current metastore.
METASTORESDescribes the current metastore.
PARAMETERSDescribes parameters of routines (functions) in the catalog.
PROVIDERSDescribes providers.
RECIPIENT_ALLOWED_IP_RANGESLists allowed IP ranges for recipients.
RECIPIENT_TOKENSLists tokens for recipients.
RECIPIENTSDescribes recipients.
REFERENTIAL_CONSTRAINTSDescribes referential (foreign key) constraints defined in the catalog.
ROUTINE_COLUMNSDescribes result columns of table valued functions.
ROUTINE_PRIVILEGESLists principals that have privileges on the routines in the catalog.
ROUTINESDescribes routines (functions) in the catalog.
ROW_FILTERSDescribes row filters on tables in the catalog.
SCHEMA_PRIVILEGESLists principals that have privileges on the schemas in the catalog.
SCHEMA_TAGSContains schema tagging metadata within the schema.
SCHEMA_SHARE_USAGEDescribes the schemas referenced in shares.
SCHEMATADescribes schemas within the catalog.
SHARE_RECIPIENT_PRIVILEGESDescribes the recipients granted access to shares.
SHARESDescribes shares.
STORAGE_CREDENTIAL_PRIVILEGES[Deprecated] Lists principals that have privileges on the storage credentials.
STORAGE_CREDENTIALS[Deprecated] Describes storage credentials.
TABLE_CONSTRAINTSDescribes metadata for all primary and foreign key constraints within the catalog.
TABLE_PRIVILEGESLists principals that have privileges on the tables and views in the catalog.
TABLE_SHARE_USAGEDescribes the tables referenced in shares.
TABLE_TAGSContains table tagging metadata within a table.
TABLESDescribes tables and views defined within the catalog.
VIEWSDescribes view specific information about the views in the catalog.
VOLUMESDescribes volumes defined in the catalog.
VOLUME_PRIVILEGESLists principals that have privileges on the volumes in the catalog.
VOLUME_TAGSContains 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

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

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

SQL
> SELECT table_schema, count(table_name)
FROM system.information_schema.tables
WHERE table_schema = 'tpch'
GROUP BY table_schema
ORDER BY 2 DESC