Query to get the row counts of all the tables in a database in Snowflake

Question

What is the query to get the row counts of all the tables in Snowflake database.

Answer 1

Please use below query:

SELECT t.table_schema || '.' ||  t.table_name as "table_name",t.row_count
FROM information_schema.tables t
WHERE t.table_type = 'BASE TABLE'
ORDER BY t.row_count

Answer 2

I'd take a different approach and use the TABLES view in the ACCOUNT_USAGE share. There may be some latency, but for larger Snowflake accounts I leverage the ACCOUNT_USAGE share all the time for things like this.

The following gives you the record count, a "list" of the table names, and the number of tables for a given Database and schema, you can tweak the query to meet your needs.

SELECT SUM(row_count) total_row_count, listagg(table_name, ' ') tab_list, count(*) num_tabs
FROM   snowflake.account_usage.tables
WHERE  table_catalog = 'DB NAME HERE'
AND    table_schema = 'SCHEMA NAME HERE'
AND    table_type = 'BASE TABLE'
AND    deleted IS NULL;

https://docs.snowflake.net/manuals/sql-reference/account-usage/tables.html

I hope this helps...Rich Murnane