SQL Query to find number of tables available in Snowflake account(including all DB and schemas)

Question

SQL Query to get total number of tables available in Snowflake account(including all DB and schemas)

Answer 1

You can query account_usage.tables or information_schema.tables views to find the total number of tables:

select count(*) from information_schema.tables;

https://docs.snowflake.com/en/sql-reference/info-schema/tables.html

select count(*) from snowflake.account_usage.tables;

https://docs.snowflake.com/en/sql-reference/account-usage/tables.html

Answer 2

There are three ways:

1. You can query the view INFORMATION_SCHEMA.TABLES to find all tables of your current database. So: You have to write a SELECT COUNT(*) FROM [database].INFORMATION_SCHEMA.TABLES for each of your databases, do a UNION ALL afterwards and SUM() your results per database to get the whole number of tables in all databases.

2. You can query the view ACCOUNT_USAGE.TABLES to find all tables and views of your account. One row represents one table. As ACCOUNT_USAGE.TABLES also contains views, you have to add a WHERE-Klause for the attribute TABLE_TYPE. Here you also have to keep in mind that you may have a latency of 90 minutes.

3. Run SHOW TABLES IN ACCOUNT; to see all tables

More infos about INFORMATION_SCHEMA.TABLES: https://docs.snowflake.com/en/sql-reference/info-schema/tables.html

More infos about ACCOUNT_USAGE.TABLES: https://docs.snowflake.com/en/sql-reference/account-usage/tables.html

More infos about SHOW TABLES: https://docs.snowflake.com/en/sql-reference/sql/show-tables.html

Note: For all three ways you can only see objects for which your current role has access privileges.