snowflake-cloud-data-platform

How to filter "show tables"

I would like to filter the output of show tables. The documentation has one example on how to do this using result_scan(last_query_id()), but for me the example does not work:

show tables;
select  "schema_name", "name" as "table_name", "rows"
from table(result_scan(last_query_id()))
where "rows" = 0;

SQL compilation error: error line 1 at position 8 invalid identifier 'SCHEMA_NAME'

The column SCHEMA_NAME is actually in the output of show tables, so I do not understand what is wrong.

Solution

  • Run the following on your account and see what it is set to:

    show parameters like 'QUOTED_IDENTIFIERS_IGNORE_CASE';

    If this is set to TRUE, then it is ignoring the quotes in your query, which will then uppercase the column names, which won't match to the lowercase names of the SHOW output.

    To resolve for your own session, you can run the following:

    ALTER SESSION SET QUOTED_IDENTIFIERS_IGNORE_CASE = False;

    You can also change this at a user or account level, if you wish. Setting this value to TRUE isn't recommended for the reason that you are running into.