topic Re: QUESTION::SQL ENDPOINT::INFORMATION_SCHEMA::QUERY RETURN NOTHING in Data Engineering

QUESTION::SQL ENDPOINT::INFORMATION_SCHEMA::QUERY RETURN NOTHING

Element115 — Fri, 24 May 2024 17:11:32 GMT

Why do the following 2 TSQL queries return an empty result set? Also nothing with a WHERE clause on a table name in the LH.

SELECT * FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE

SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE

Re: QUESTION::SQL ENDPOINT::INFORMATION_SCHEMA::QUERY RETURN NOTHING

Anonymous — Fri, 24 May 2024 17:19:05 GMT

Hi Element115
Thanks for using Fabric Community.
If you have any views in the lakehouse, then the queries will return some data.

For example:
I had no views in Lakehouse_2:

In other case I created a view in another lakehouse:

Hope this helps. Please let me know if you have any other questions.

Re: QUESTION::SQL ENDPOINT::INFORMATION_SCHEMA::QUERY RETURN NOTHING

Element115 — Fri, 24 May 2024 17:21:57 GMT

Oh I am sorry. It's the column and table usage of any VIEWS. I was reading this as the verb 'to view' the column and/or table usages of tables, not views. Need another coffee. 😁 I have no views, so ofc it's empty. Thanks.

Re: QUESTION::SQL ENDPOINT::INFORMATION_SCHEMA::QUERY RETURN NOTHING

Element115 — Fri, 24 May 2024 17:32:34 GMT

Actually, I take back my previous answer. I just tried running both TSQL queries on a LH that has a view, and as you can see in my screenshot, it does not work on my end:

Re: QUESTION::SQL ENDPOINT::INFORMATION_SCHEMA::QUERY RETURN NOTHING

Anonymous — Fri, 24 May 2024 17:35:32 GMT

Hi Element115

How did you create the view? Can you please share the code here?

If you create the view using the tables present in lakehouse, then it would be shown in the sql query result.

Re: QUESTION::SQL ENDPOINT::INFORMATION_SCHEMA::QUERY RETURN NOTHING

Element115 — Fri, 24 May 2024 17:36:03 GMT

CREATE VIEW vTABLE_NAMES AS SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' ;

Re: QUESTION::SQL ENDPOINT::INFORMATION_SCHEMA::QUERY RETURN NOTHING

Anonymous — Fri, 24 May 2024 17:43:39 GMT

Hi Element115
Create a view using table present in lakehouse and retry.

Re: QUESTION::SQL ENDPOINT::INFORMATION_SCHEMA::QUERY RETURN NOTHING

Element115 — Fri, 24 May 2024 17:51:52 GMT

In this case, with a VIEW on a user created table, it works.

But why does it not work for VIEWs on the INFORMATION_SCHEMA? Ahhh because they are all views in this schema and not tables. Duh.

Re: QUESTION::SQL ENDPOINT::INFORMATION_SCHEMA::QUERY RETURN NOTHING

Anonymous — Fri, 24 May 2024 17:55:05 GMT

Hi Element115

The information_schema.VIEW_TABLE_USAGE is an information schema view that returns one row for each table in the current database that is used in a view. Here the current database is the lakehouse. So if the view is created using the table present in lakehouse, then the data will be displayed in the information schema. This view provides information about the objects to which the current user has permissions.

Here are some of the columns you might find in information_schema.VIEW_TABLE_USAGE:

VIEW_CATALOG: View qualifier.
VIEW_SCHEMA: Name of schema that contains the view.
VIEW_NAME: View name.
TABLE_CATALOG: Table qualifier.
TABLE_SCHEMA: Name of schema that contains the base table.
TABLE_NAME: Base table that the view is based on.

Hope this helps.