Forum Discussion

Element115's avatar
Element115
Memorable Member
2 years ago
Solved

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

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       SEL...
data engineering
  • Anonymous's avatar
    Anonymous
    2 years ago

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

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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

 

Element115's avatar
Element115
Memorable Member
2 years ago

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's avatar
    Anonymous
    Not applicable
    2 years ago

    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.