Explore database objects

This article details how you can discover and explore catalogs, schemas, tables, and other database objects in Azure Databricks. The instructions in this article focus on returning details for database objects that you have at least the BROWSE or SELECT privilege on.

For general information on Unity Catalog privileges, see Unity Catalog privileges and securable objects. For information about how to set schema ownership and permissions, see Manage Unity Catalog object ownership and Manage privileges in Unity Catalog.

Most access to database objects is governed by Unity Catalog, but your company might use another data governance approach or combine Unity Catalog with other legacy table ACLs. This article focuses on describing behavior for objects governed by Unity Catalog, but most methods described in this article also work for database objects that aren't governed by Unity Catalog.

This article includes instructions for Catalog Explorer and SQL. Select the Catalog icon in the workspace side bar to access Catalog Explorer. You can execute SQL commands from a notebook or the query editor attached to compute. To view database objects with Catalog Explorer, you must have at least the BROWSE privilege on the objects. To view database objects with SQL, you must have at least the SELECT privilege on the object, as well as USE CATALOG on the parent catalog and USE SCHEMA on the parent schema.

Filtering database objects

During interactive exploration of database objects with Catalog Explorer, you can use the provided text box to filter results. Matched strings in object names are highlighted, but only among currently visible database objects. For complete search of all database objects, see Search for workspace objects.

SQL provides similar functionality by optionally specifying a regex_pattern clause in conjunction with a SHOW statement, such as the following:

SHOW TABLES IN schema_name LIKE 'sales_*_fy23'

Explore catalogs

Catalogs represent the top level of data governance in each Unity Catalog metastore.

SQL

Run the following command to see a list of catalogs available to you.

SHOW CATALOGS

See SHOW CATALOGS.

Catalog Explorer

When you access Catalog Explorer, you see a list of catalogs available to you.

Select a catalog

SQL

Run the following command to set your currently active catalog.

USE CATALOG catalog_name

See USE CATALOG.

Catalog Explorer

Click on a catalog name to select it.

See catalog details

SQL

Run the following command to describe a catalog.

DESCRIBE CATALOG catalog_name

See DESCRIBE CATALOG.

Catalog Explorer

Select the Details tab to review catalog details.

Explore schemas

Schemas are collections of tables, views, volumes, functions, and models in Unity Catalog. Schemas are contained in catalogs.

SQL

Run the following command to see a list of schemas available to you.

SHOW SCHEMAS IN catalog_name

See SHOW SCHEMAS.

Catalog Explorer

When you select a catalog in Catalog Explorer, you see a list of available schemas.

Select a schema

SQL

Run the following command to set your currently active schema.

USE schema catalog_name.schema_name

See USE SCHEMA.

Catalog Explorer

Click on a schema name to select it.

See schema details

SQL

Run the following command to describe a schema.

DESCRIBE SCHEMA schema_name

See DESCRIBE SCHEMA.

Catalog Explorer

Select the Details tab to review schema details.

Explore tables and views

Tables and views are contained in schemas. Most tables in Unity Catalog are backed by Delta Lake, but you might also have access to tables registered against external data. See What data can you query with Azure Databricks?.

Views in Unity Catalog always reference data in another table.

SQL

Run the following command to see a list of tables available to you.

SHOW TABLES IN catalog_name.schema_name

Run the following command to see a list of tables available to you.

SHOW VIEWS IN catalog_name.schema_name

See SHOW TABLES and SHOW VIEWS.

Catalog Explorer

When you select a schema in Catalog Explorer, you see a list of available tables and views.

View table contents and details

You can view most table details with either Catalog Explorer or SQL. Some details are only available in the Catalog Explorer UI.

Select a table in Catalog Explorer to explore table details.

Explore table columns

SQL

Run the following command to view table columns.

SHOW COLUMNS IN table_name

See SHOW COLUMNS.

Catalog Explorer

Select the Columns tab to view table columns.

View sample data

SQL

Run the following command to view 1000 records from a table.

SELECT * FROM table_name LIMIT 1000;

See Query data.

Catalog Explorer

Select the Sample Data tab to view sample data. You must have access to active compute to sample data.

See table details

SQL

Run the following command to describe a table.

DESCRIBE TABLE table_name

Run the following command to display table properties for a table.

SHOW TBLPROPERTIES table_name

See DESCRIBE TABLE and SHOW TBLPROPERTIES.

Catalog Explorer

Select the Details tab to review table details, including table properties.

View table history

Table history is available for Delta tables. All Unity Catalog managed tables are Delta tables.

SQL

Run the following command to review table history.

DESCRIBE HISTORY table_name

See DESCRIBE HISTORY.

Catalog Explorer

Select the History tab to review table history.

View frequent queries and users

If the table is registered in Unity Catalog, you can view the most frequent queries made on the table and users who accessed the table in the past 30 days using Catalog Explorer. See View frequent queries and users of a table.

View primary key and foreign key relationships

For tables with foreign keys defined, click View relationships at the top-right of the Columns tab. The Entity Relationship Diagram (ERD) opens. The ERD displays the primary key and foreign key relationships between tables in a graph, providing a clear and intuitive representation of how data entities connect.

For more information about primary key and foreign key constraints, see Constraints on Azure Databricks.