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

[RichardSCarchit]

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!

[siddhathPanchal]

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]

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]

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]

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 )

[Priyag1]

Refer documentation once