Explore database objects

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

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

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

Note

You can navigate Unity Catalog-governed database objects in Catalog Explorer without active compute. To explore data in the hive_metastore and other catalogs not governed by Unity Catalog, you must attach to compute with appropriate privileges.

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.

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

SHOW CATALOGS

See SHOW CATALOGS.

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

Select a catalog

Run the following command to set your currently active catalog.

USE CATALOG catalog_name

See USE CATALOG.

Click on a catalog name to select it.

See catalog details

Run the following command to describe a catalog.

DESCRIBE CATALOG catalog_name

See DESCRIBE CATALOG.

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.

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

SHOW SCHEMAS IN catalog_name

See SHOW SCHEMAS.

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

Select a schema

Run the following command to set your currently active schema.

USE schema catalog_name.schema_name

See USE SCHEMA.

Click on a schema name to select it.

See schema details

Run the following command to describe a schema.

DESCRIBE SCHEMA schema_name

See DESCRIBE SCHEMA.

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 Databricks?.

Views in Unity Catalog always reference data in another table.

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.

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

Note

If the schema has other database objects like volumes present, you might need to click Tables to expand the list of 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

Run the following command to view table columns.

SHOW COLUMNS IN table_name

See SHOW COLUMNS.

Select the Columns tab to view table columns.

View sample data

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

SELECT * FROM table_name LIMIT 1000;

See Query data.

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

See table details

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.

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.

Run the following command to review table history.

DESCRIBE HISTORY table_name

See DESCRIBE HISTORY.

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 View relationships button 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.

Entity relationship diagram

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