How to get a list of all tables from a Snowflake database / schema using C# in Visual Studio

Question

I'm able to run this query in Snowflake with no issues:

select TABLE_NAME
from information_schema.tables
where table_catalog = 'DB' and table_schema = 'SCHEMA';

But when running the same query through Visual Studio using C#, the reader seems to be empty:

string Query = "select TABLE_NAME from information_schema.tables where table_catalog = 'DB' and table_schema = 'SCHEMA';";

IDbCommand Cmd = Conn.CreateCommand();
Cmd.CommandText = Query;

IDataReader Reader = Cmd.ExecuteReader();

while (Reader.Read())
{
    Console.WriteLine("test");   // this never gets printed!
}

However, I am able to read data from queries for regular tables like SELECT * FROM TABLE;

Is there a way to read from information_schema in Visual Studio? Or an alternative way to get a list of all the tables in a Snowflake database/schema?

Answer 1

This query should return the table information you require from information_schema:

select table_schema, table_name
from information_schema.tables
where table_type = 'BASE TABLE'

You do not state which Snowflake driver you are using. The SQL should be independent but the means of executing the query might differ, or there might be built-in assistance available with some drivers.

(Warning: I have encountered several problems with the Snowflake.Data library.)