Row count tables in schema

Question

Is there a way to get a row count of all the tables in a snowflake schema without using the information schema or account usage schema.

Does this answer your question? [Fastest way to count exact number of rows in a very large table?](https://stackoverflow.com/questions/6069237/fastest-way-to-count-exact-number-of-rows-in-a-very-large-table) — Elikill58, Nov 30 '21 at 12:49
no i want to loop through a schema which has 130 tables and get the table name and row count from each table in the schema — Suhayr, Nov 30 '21 at 14:02

devops21 · Answer 1 · 2021-11-30T19:42:44.290

1

You can run show tables command and that will give you name of the table ("name" column) and number of rows ("rows" column).

SHOW TABLES;

edited Nov 30 '21 at 19:42

answered Nov 30 '21 at 16:46

devops21

21
4

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Nov 30 '21 at 18:36

Himanshu · Answer 2 · 2021-11-30T21:29:23.830

0

Use count and a union

  Select 'tablename', count(*) from 
   Yourschema.tablename
   union
  Select 'tablename1', count(*) from
   Yourschema.tablename1

Alternatively, can generate the same above sqls via information schema for more tables

    Select 'Select ' || tablename || ', count(*)
      from tablename union ' from
     information_schema where
     schema like '%snowflake%'

This will generate your sqls for all tables. You just have to remove the last extra union in the output and can copy and execute that output. Or you can spool this output to a .sql file and execute that file.

edited Nov 30 '21 at 21:29

answered Nov 30 '21 at 13:06

Himanshu

3,830
2
10
29

Hi, thanks for responding. There are 130 tables in the schema so this is not the most efficient way. I need something that will not take a long time to query – Suhayr Nov 30 '21 at 14:01
Have edited check once if that helps – Himanshu Nov 30 '21 at 21:29

score 0 · Answer 3 · answered Nov 30 '21 at 14:37

There is no way except using INFORMATION_SCHEMA, ACCOUNT_USAGE or SELECT COUNT(*) UNION ....

So... I would recommend querying the TABLES-View from Information_Schema or Account_Usage: https://docs.snowflake.com/en/sql-reference/info-schema/tables.html

EDIT: You can make it unneccessarily complex and do the following: show tables in schema myschema; and then loop over the result to issue a SELECT COUNT(*) per table and sum them.

Row count tables in schema

3 Answers3