CODEWITHSUNDEEP
databasesnowflake-cloud-data-platform
Эльфия Валиева
Эльфия Валиева

Reputation: 156

Snowflake, select tables count error: Information schema query returned too much data

I use database Snowflake. Schema has more 15000 tables. And I tried to get information about tables count in my current schema by following query:

SELECT COUNT(T.table_name) as tnum FROM information_schema.TABLES T WHERE 
 T.table_schema = 'MYSCHEMA' 
 AND T.TABLE_CATALOG = 'MYDB' 
 AND T.table_type = 'BASE TABLE'

And get the following error: "Information schema query returned too much data. Please repeat the query with more selective predicates."

Do you know how to solve this kind of error?

Upvotes: 0

Views: 463

Answers (3)

Nirali Patel
Nirali Patel

Reputation: 1

   // count of all the tables in the given database and schema. (Excludes dropped tables)
   SELECT count(T.table_name)
   FROM   snowflake.account_usage.tables T
   WHERE  table_catalog = 'DB_NAME'
   AND    table_schema = 'SCHEMA_NAME'
   AND    table_type = 'BASE TABLE'
   AND    deleted IS NULL // This filters the dropped table

Upvotes: 0

Damini Suthar
Damini Suthar

Reputation: 1492

SELECT COUNT(*) as tnum FROM information_schema.TABLES T WHERE  
T.table_schema = 'MYSCHEMA'   AND T.TABLE_CATALOG = 'MYDB'   AND
T.table_type = 'BASE TABLE'

Use this query code and try once.

Upvotes: 0

Iqra Ijaz
Iqra Ijaz

Reputation: 281

You can run the same query on Account usage schema and it should work

SELECT COUNT(T.table_name) as tnum FROM snowflake.account_usage.TABLES T WHERE 
 T.table_schema = 'MYSCHEMA' 
 AND T.TABLE_CATALOG = 'MYDB' 
 AND T.table_type = 'BASE TABLE'

Upvotes: 1

Related Questions