'How to get count of tables present in a particular schema in snowflake?

I am trying to find the count of tables present in CDC schema, I tried with information_schema but the information_schema is empty for me.

enter image description here

sqlsnowflake-cloud-data-platformsnowflake-schema


Solution 1:[1]

The following SQL would give you the required results:

use schema <schemaname>;
show tables;

The result shows the number of tables in the schema. enter image description here

Solution 2:[2]

Note: The information schema in a particular database only displays the objects to which your current role in the session has access.

Please make sure that the role you are using has required access control privileges to view the database objects. If not, if you have access to high privilege roles like SYSADMIN/ACCOUNTADMIN you could use them when viewing metadata information in Snowflake.

You can try querying the INFORMATION_SCHEMA.TABLES View with a query :

select count(distinct TABLE_NAME)
from <database_name>.INFORMATION_SCHEMA.TABLES
where TABLE_CATALOG = <database_name>
and TABLE_SCHEMA = <schema_name>;

Alternatively, you can also use the SHOW TABLES command as follows to get the count.

show tables in schema <DATABASE_NAME>.<SCHEMA_NAME>;
select count(*) from table(result_scan('<last_query_id>'));

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Srinath Menon
Solution 2 Nikita Kulkarni

Related Questions