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

[Element115]

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

[Anonymous]

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.

[Element115]

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:

[Anonymous]

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.

[Element115]

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

[Anonymous · Accepted solution]

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

[Element115]

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.

[Anonymous]

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. 

[Element115]

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.