RetroSearch Browse

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-information-schema below:

Information schema | Databricks Documentation

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â Notesâ

A manual sync using REPAIR TABLE may be required for some catalog metadata changes to be reflected in the information schema. For more information, see REPAIR TABLE.
All identifiers except column and tag names are stored in the information schema as lowercase STRING. For query performance, avoid using functions like LOWER() or UPPER() on the identifier column. Instead, compare identifiers directly using lowercase values.
To prevent query timeouts, apply selective filters when querying the information schema (e.g. WHERE table_catalog = 'main' AND table_schema = 'default'). Refer to the documentation above for a full list of columns that can be used as filters for each Information Schema table.
- Note that LIMIT pushdown is not currently supported, so although it can truncate the results it will not improve performance.

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