Forum Discussion

Element115's avatar
Element115
Icon for Memorable Member rankMemorable 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.

     

Element115's avatar
Element115
Icon for Memorable Member rankMemorable Member
2 years ago

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

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's avatar
    Element115
    Icon for Memorable Member rankMemorable Member
    2 years ago
    CREATE VIEW vTABLE_NAMES 
AS 
SELECT * 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
;
    • 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
        Icon for Memorable Member rankMemorable 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.