SHOW TABLES in Snowflake

Problems encountered

Execute the following SQL in Snowflake to query the tables contained in the database.

1

SHOW TABLES;

When my database structure is as follows:

1
2
3
4
5
6
7
8

Snowflake Schema Test(database)
    JAVEN(schema)
        TestTable(table)
        TestTable1(table)
    PUBLIC(schema)
        TestTable(table)
        TestTable1(table)
        TestTable2(table)

At this point, the result of executing SHOW TABLES is as follows:

Obviously, there are 5 tables in the database, but the query comes up with only 3 tables.

Cause of the problem

We guessed that it might be because there are renamed tables under different Schema, and Snowflake did the de-duplication. But this is not true, the official explanation for this is as follows:

In the output, results are sorted by database name, schema name, and then table name. This means results for a database can contain tables from multiple schemas and might break pagination. In order for pagination to work as expected, you must execute the SHOW TABLES command for a single schema. You can use the IN SCHEMA <schema_name> parameter to the SHOW TABLES command. Alternatively, you can use the schema in the current context by executing a USE SCHEMA command before executing a SHOW TABLES command.

References: https://docs.snowflake.com/en/sql-reference/sql/show-tables#usage-notes

It means that only executing this SQL against a Schema is supported.

Solution

When our business must require a single SQL query for all the tables under that database, it is obviously wrong to use SHOW TABLES directly.

But Snowflake’s documentation has some optional parameters for SHOW TABLES:

[ IN … ]
 Optionally specifies the scope of the command. Specify one of the following:
 
 DATABASE,
 DATABASE <db_name>
  Returns records for the current database in use or for a specified database (<db_name>).
  If you specify DATABASE without <db_name> and no database is in use, the keyword has no effect on the output.
 
 Schema,
 SCHEMA <schema_name>,
 <schema_name>
  Returns records for the current schema in use or a specified schema (<schema_name>).
  SCHEMA is optional if a database is in use or if you specify the fully qualified <schema_name> (for example, db.schema).
  If no database is in use, specifying SCHEMA has no effect on the output.

References: https://docs.snowflake.com/en/sql-reference/sql/show-tables#parameters

So when we use the following SQL:

1

SHOW TABLES IN DATABASE;

Since no database is specified, the current default database is used, which happens to be in the range we expect.

The return result is as follows: