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

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.

CodePudding user response：

The following SQL would give you the required results:

use schema <schemaname>;
show tables;

The result shows the number of tables in the schema.

CodePudding user response：

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>'));