CODEWITHSUNDEEP
sqlamazon-redshift
noober
noober

Reputation: 1505

a query to count all records of the same tablename but different schemas

I need a query or a script that will do the generate the following: 

select count(*) from events_abc;
select count(*) from events_def;
select count(*) from events_123;

Ideally i would like to do a sum of all the counts,but if I can't then a list of all the counts would be a nice start.

I need to do this for Amazon Redshift. The catch is I have many different schemas which can have the same table name.

So schema john__foo can have a table name events_abc and schema mary__foo can also have a table named events_abc.

Would I use some kind of cursor? What would be the best way to script this?

Upvotes: 1

Views: 431

Answers (1)

Thomas G
Thomas G

Reputation: 10226

From Amazon RedShift Dev Guides - Examples of Catalog Queries

List the Schemas and Tables in a Database

The following query joins STV_TBL_PERM to some PG tables to return a list of tables in the TICKIT database and their schema names (NSPNAME column). The query also returns the total number of rows in each table. (This query is helpful when multiple schemas in your system have the same table names.)

select datname, nspname, relname, sum(rows) as rows
from pg_class, pg_namespace, pg_database, stv_tbl_perm
where pg_namespace.oid = relnamespace
and pg_class.oid = stv_tbl_perm.id
and pg_database.oid = stv_tbl_perm.db_id
and datname ='tickit'
group by datname, nspname, relname
order by datname, nspname, relname;

datname | nspname | relname  |  rows
--------+---------+----------+--------
tickit  | public  | category |     11
tickit  | public  | date     |    365
tickit  | public  | event    |   8798
tickit  | public  | listing  | 192497
tickit  | public  | sales    | 172456
tickit  | public  | users    |  49990
tickit  | public  | venue    |    202
(7 rows)

This is more or less what you are looking to do, isn't it ?

So just play around with this query. If you are already connected to the right database, and want to list all the tables begining with event_ in all schemas, you should do this :

SELECT datname, nspname, relname, SUM(rows) AS rows
FROM pg_class, pg_namespace, pg_database, stv_tbl_perm
WHERE pg_namespace.oid = relnamespace
  AND pg_class.oid = stv_tbl_perm.id
  AND pg_database.oid = stv_tbl_perm.db_id
  AND relname LIKE 'event_%'
GROUP BY datname, nspname, relname
ORDER BY datname, nspname, relname;

Upvotes: 2

Related Questions