Skip to main content

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

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:

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

SQL
SHOW CATALOGS

See SHOW CATALOGS.

Select a catalog​

Run the following command to set your currently active catalog.

SQL
USE CATALOG catalog_name

See USE CATALOG.

See catalog details​

Run the following command to describe a catalog.

SQL
DESCRIBE CATALOG catalog_name

See DESCRIBE CATALOG.

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.

SQL
SHOW SCHEMAS IN catalog_name

See SHOW SCHEMAS.

Select a schema​

Run the following command to set your currently active schema.

SQL
USE schema catalog_name.schema_name

See USE SCHEMA.

See schema details​

Run the following command to describe a schema.

SQL
DESCRIBE SCHEMA schema_name

See DESCRIBE SCHEMA.

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.

SQL
SHOW TABLES IN catalog_name.schema_name

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

SQL
SHOW VIEWS IN catalog_name.schema_name

See SHOW TABLES and SHOW 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.

SQL
SHOW COLUMNS IN table_name

See SHOW COLUMNS.

View sample data​

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

SQL
SELECT * FROM table_name LIMIT 1000;

See Query data.

See table details​

Run the following command to describe a table.

SQL
DESCRIBE TABLE table_name

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

SQL
SHOW TBLPROPERTIES table_name

See DESCRIBE TABLE and SHOW TBLPROPERTIES.

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.

SQL
DESCRIBE HISTORY table_name

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

Last updated on