How can I view row counts of all tables in a Snowflake database?

In this article, you will learn how to view row counts of all tables in a Snowflake database. Data like row counts are typically stored in a metadata table in most databases.

Metadata is commonly called “data about the data”. To get the row count for each table we just need to figure out how to access table metadata in Snowflake. We will do this a few different ways, since each method has pros and cons. We will also see how to get additional metadata about each table and how to filter table metadata in additional ways, not just by database.

But first… Table Row Counts in Datameer

In Datameer, there is no coding required to get the row count for each table. You can simply navigate to the Datasets tab in the Workbench and you will be able to view the row count for each table.

Method 1. The easy way: show tables

This first method is the easiest method.

show tables in database <database_name>;

If you look at the example below, you can see Snowflake returns some basic metadata, including row count for each table. If you just want to view row counts, this method works. However, you can’t use the output of this method in a query, so you can’t filter or use this method in a data pipeline process.

Example:

show tables in database snowflake_sample_data;

Query show tables output with result_scan()

You access a result set of command show tables by using the function result_scan(). This converts output of command show tables into a regular table that you query just like any other table. However, the function result_scan() uses query_id as the only argument. This means you must first run the command show table before you use the function result_scan(). Use the function last_query_id to get the query id of command show tables. Be careful that you don’t run another query before you use result_scan().

If you run a query and get an error, that still counts as a query and result_scan() will fail with an error.

Query 01a2069f-0601-1c0b-0051-2283000365d6 has no result because it failed

Example:

show tables in database snowflake_sample_data;
select * from table(result_scan(last_query_id()))

Method 2: Database Information Schema

Each database has schema information_schema which contains metadata about the database in which it resides. If you do not see schema information_schema in your database, you need usage access granted to schema information_schema and select access granted to at least view tables.

select * from <database_name>.information_schema.tables;

Example:

select * from snowflake_sample_data.information_schema.tables`;

You might notice there are more columns AND rows in this result set than the last method in the example output. information_schema.tables has more columns because it includes more metadata than show tables. information_schema.tables has more rows because views are also included in the output. To limit the result set to only tables with at least 1 record, add where row_count > 0.

Example:

select * from snowflake_sample_data.information_schema.tables where row_count > 0`;

You can query the information_schema tables just like you would any other table, so it is a little more flexible than the previous method.

Method 3: Snowflake Account Usage Schema

The last method is the only method that allows you to get row counts for all tables in your Snowflake instance. However, accessing this table often requires administrator permissions.

select * from snowflake.account_usage.tables;

To filter the results set by database use where table_catalog = ‘<database_name>’.

Example:

select * from snowflake.account_usage.tables where catalog_name = 'temp';

This method only includes databases that are owned by the Snowflake account. Shared databases will not be in the output using this method.

This method also includes deleted tables. To filter out deleted tables, add where deleted is null

Conclusion

We have learned how to view row counts of all tables in a Snowflake database. There are a couple of ways to get metadata, including row counts, on tables in Snowflake. Most of the Snowflake methods utilize SQL and also require that relevant permissions are granted. Datameer allows you to see metadata right in the workbench, with no code and no permissions to worry about.

This article was written by Nick Neal

Continue reading

Check out: Ideal Development Environment Setup for Snowflake