Row count tables in schema-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

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;

CodePudding user response：

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.