cancel
Showing results for 
Search instead for 
Did you mean: 

How to query object ID in Databricks SQL warehouse using only SQL?

RichardSCarchit
New Contributor III

I can see on Databricks SQL warehouse Data tab that clusters, catalogs and schemas have a unique ID. User created tables, views and functions must have and unique ID too, but it is not exposed to the user as far as I can tell.

I need to retrieve the IDs for all these objects for a data governance solution I'm working on. information_schema does not show any object ID.

Is there a way to do this (like in Oracle for example, query the ALL_OBJECTS system view). Thanks!

6 REPLIES 6

User16869361351
Contributor

Hi @Richard Architect​ .Thank you for asking this question. I reviewed the information schema structure.It appears that we are not exposing the ID column.Currently, I am checking internally to see if there are any alternatives.

RichardSCarchit
New Contributor III

Thanks you please inform if there is any progress. The information_schema views are very good and I use them regularly but it would be nice if they were expanded to be closer to oracle system views (object dependencies would be great, although we can query it via constraint_column for tables, it would be nice to see view dependencies)

Faithxza
New Contributor II

Note that the specific syntax and naming conventions may differ depending on the SQL dialect used by Databricks SQL warehouse, so it's always a good idea to consult the documentation or resources specific to your environment. AIM Provider Portal Login

luis_herrera
Contributor

Unfortunately, the object ID for user-created tables, views, and functions is not exposed to the user in the Databricks SQL warehouse. The  information_schema does not show any object ID.

(https://docs.databricks.com/sql/language-manual/sql-ref-information-schema.html)

However, you can get object_id from the unity catalog API. To do so, you can use the GET /preview endpoint of the Unity Catalog API to retrieve a preview of the catalog object. The response will include the object_id property, which is a unique identifier for the object. You can then use this object_id to perform other operations on the catalog object. For more information, you can refer to the Databricks documentation on Unity Catalog API.

(PS: check out #DAIS2023 talks )

Welcome to Databricks Community: Lets learn, network and celebrate together

Join our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections. 

Click here to register and join today! 

Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.