Databricks SQL Information Schema
The INFORMATION_SCHEMA serves as a standardized SQL schema available in every catalog within the Unity Catalog.
Contained within the INFORMATION_SCHEMA are a collection of views that detail the objects accessible within the catalog associated with the schema. These views provide information exclusively on objects that you have the privilege to access. It's important to note that the INFORMATION_SCHEMA of the SYSTEM catalog supplies information regarding objects across all catalogs present within the metastore, but it does not contain metadata for hive_metastore objects.
The primary function of the INFORMATION_SCHEMA is to offer a SQL-based, self-explanatory API for accessing and understanding metadata.
The following entity-relationship (ER) diagram provides an overview of a subset of information schema views and how they relate to each other.
You can refer to each table using the below reference documentation link for Azure and AWS.
Now let's get some practical implementations for the same. I am stating some of the examples that have usually been used in the recent past in the below article. /using-information-schema-in-unity-catalog