INFORMATION_SCHEMA IS NOT POPULATED WITH TABLE INFORMATION

[Databricks3]

I have created a metastore and within that metastore i have created multiple schemas and tables underlying it but none of table details is visible from information schema. All the tables are empty.

Could you please let me know if I am missing here. Below query is returning no data.

%sql
select * from <catalog_name>.information_schema.tables

[-werners-]

I think that it only applies to Unity.

If you want to list the tables of a schema; you can use SHOW TABLES (after USE <schema>)

[Databricks3]

I am using Unity Catalog pointing to s3

[-werners-]

Could be a permission issue or a wrong query,

can you try this query:

> SELECT table_owner

   FROM information_schema.tables

   WHERE table_schema = 'information_schema'

     AND table_name = 'columns';

[Databricks3]

Getting the below error message. Even if I use <catalog_name>.information_schema.tables it is not returning anything.

[Databricks3]

Getting the below result.@Werner Stinckens​ 

[-werners-]

ok so it is not a permission issue.

Meaning your query needs change.

What exactly do you want to see?

Here you can find the ERD

[Databricks3 · Accepted solution]

I can access all the tables if I use system.information_schema instead of catalog_name.information_schema. But my point is why it is working in this way. When I am creating a catalog, a information_schema is created inside it by default.

And it is having no information of the same catalog.

Anyway now I can access it using system. Thanks a lot 🙂 @Werner Stinckens​ 

[-werners-]

no idea, I guess information_schema, being generated, follows some special logic.

[Anonymous]

Hi @SK ASIF ALI​ 

Thank you for posting your question in our community! We are happy to assist you.

To help us provide you with the most accurate information, could you please take a moment to review the responses and select the one that best answers your question?

This will also help other community members who may have similar questions in the future. Thank you for your participation and let us know if you need any further assistance! 

[EwenGillies]

I noticed this issue is currently caused when you rename a catalog.  The contents of <catalog>.information_schema are all views like this:

SELECT
  *
FROM
  system.information_schema.columns
WHERE
  table_catalog = '<catalog>'

If you rename the catalog, the view definitions don't get updated.  For example, if you made a catalog named `my_awesome_catalog`, then the view in `my_awesome_catalog.information_schema` will be:

SELECT
  *
FROM
  system.information_schema.columns
WHERE
  table_catalog = 'my_awesome_catalog'

If you then renamed `my_awesome_catalog` to `my_new_named_catalog`, the view inside `my_new_named_catalog.information_schema` will remain unchanged, and will still be:

SELECT
  *
FROM
  system.information_schema.columns
WHERE
  table_catalog = 'my_awesome_catalog'

Assuming you no longer have a catalog named `my_awesome_catalog`, this will return zero rows.  Even worse, if you make a new catalog named `my_awesome_catalog`, the `my_new_named_catalog.information_schema` views will return the wrong information completely.

Not great overall, definitively an easily identifiable bug in Databricks.

[John_Gooding]

I can confirm this as a bug, either remove the ability to rename a catalog from the UI, or fix it or provide a method to rebuild the information_Schema views.  Took me a while to track this down thinking it was a permissions issue, but turned out the creator of the catalog renamed it right after they realized their mistake instead of re-creating the catalog.

Now we have 2 days of work to do to rebuild the catalog into a new catalog since a user can't modify the infomration_schema views to resolve.

Thanks