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