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

count of all tables in snowflake
snowflake count number of rows
snowflake rowcount
snowflake get table size
snowflake tables
count if snowflake
information schema snowflake
count(distinct window function snowflake)

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

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

Number of tables by the number of rows in Snowflake, If you want to get an overview on how many rows tables in your database hold one way is to count them by row intervals. This query returns number of tables by the number of their rows grouped into predefined intervals. This query returns list of tables in a database with their number of rows. Query select t.table_schema || '.' || t.table_name as "table_name", t.row_count as "rows" from information_schema.tables t where t.table_type = 'BASE TABLE' order by t.row_count desc; Columns. table_name - table name with schema name; rows - number of rows in a table; Rows. One row represents one table; Scope of rows: all tables in a database including tables without rows

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

Find number of tables in Snowflake, Useful SQL queries for Snowflake to explore database schema. Table of Contents: Query; Columns; Rows; Sample results select count(*) as tables from information_schema.tables where table_type = 'BASE TABLE';. Code has been� Query below returns total number of tables in current database. Query select count(*) as tables from information_schema.tables where table_type = 'BASE TABLE'; Columns. tables - number of tables in a database; Rows. Query returns just one row. Sample results. Those results show that there are 87 tables in SNOWFLAKE_SAMPLE_DATA database.

select TABLE_CATALOG||'.'||TABLE_SCHEMA||'.'||TABLE_NAME FQN,ROW_COUNT from YOURDATABASE.information_schema.tables where table_type = 'BASE TABLE';

+------------------------------------------------+-----------+
| FQN                                            | ROW_COUNT |
|------------------------------------------------+-----------|
| YOURDATABASE.PUBLIC.FOO1                       |   7108958 |
| YOURDATABASE.PUBLIC.FOO2                       |    444847 |
| YOURDATABASE.PUBLIC.FOO3                       |   2048267 |
| YOURDATABASE.PUBLIC.FOO4                       |       212 |
+------------------------------------------------+-----------+

TABLES View — Snowflake Documentation, displays a row for each table and view in the specified (or current) database, including the views To view only tables in your queries, filter using a WHERE clause, e.g.: WHERE table_schema != 'INFORMATION_SCHEMA'. Examples�. Retrieve the size (in bytes) of all tables in all schemas in the mydatabase database:. Below query will give a number of rows for the required tables but these are not accurate until we ANALYZE(gather stats) the tables. So we can not depend on ALL_TABLES system table for getting accurate rows count. SELECT table_name,num_rows FROM all_tables WHERE owner = 'Schema'; So we can use below set of statements to find the count of rows of all the tables at once and store them in one permanent table for analysis purpose. CREATE TABLE stats AS SELECT table_name, num_rows FROM all_tables

MS SQL Server, How can we get the Snowflake table row counts back to MS SQL, Also, I'd prefer a script which gives counts for all tables in a database� Here, we are setting the short name A for getting table name and short name B for getting row count. See the below query for getting record count. For this example, set the top 10 rows to get only 10 table names and record counts. See the below example query. Let's start coding.

COUNT — Snowflake Documentation, which can be a qualified name (e.g. database.schema.table.column_name). If the DISTINCT keyword is used, it applies to all columns. CREATE TABLE basic_example (i_col INTEGER, j_col INTEGER); INSERT INTO SELECT i_col, COUNT(*), COUNT(j_col) FROM basic_example GROUP BY 1 row has all nulls. In this approach we will get the row counts from each of the tables in a given database in an iterative fashion and display the record counts for all the tables at once. The T-SQL query below uses the sp_MSforeachtable system stored procedure to iterate through each of the tables to capture the row count for all the tables in a database.

SHOW TABLES — Snowflake Documentation, Getting Started � Introduction to Snowflake � Tutorials, Videos & Other Resources � Release Notes The command can be used to list tables for the current/ specified database or Default: No value (all columns are included in the output) either include LIMIT rows or query the corresponding view in the Information Schema� I would suggest that you get access to the Snowflake database. It can be granted access to you as in the documentation. If you want to just use the information_schema of the databases themselves you can get the column count too by joining to the columns view: select; tabs. table_catalog, tabs. table_schema, tabs. table_name, tabs. table_type, tabs. created,

Comments
  • As Rich mentioned, this has some latency, but the biggest benefit is that you can query all databases and schemas at the same time, if you wish, while information_schema is limited to a single database (and only includes tables that you have access to).