-1

I know about the describe command \d and select count(*) from my_schema_1.my_table_1;. However I'd like to get a neat list of the entire database, I have quite a few tables. Something like below would be nice.

my_schema_1   | mytable_1   | 12323
my_schema_2   | mytable_2   | 0

I'd basically like to loop over all the tables.

basickarl
  • 37,187
  • 64
  • 214
  • 335

1 Answers1

2

Maybe something like this (no need to execute a COUNT(*)) for each table):

EDIT new version to consider tables without projections:

SELECT
    t.table_schema AS schema,
    t.table_name AS table,
    ZEROIFNULL(
        CASE WHEN p.is_segmented IS TRUE 
            THEN SUM(ps.row_count) * COUNT(DISTINCT ps.node_name) // COUNT(ps.node_name) 
            ELSE MAX(ps.row_count)
        END
    ) AS row_count,
    CASE WHEN p.is_segmented THEN 'Yes' ELSE 'No' END AS segmented,
    COUNT(DISTINCT p.projection_id) AS num_proj
FROM
    v_catalog.tables t
    LEFT OUTER JOIN v_monitor.projection_storage ps
        ON t.table_id = ps.anchor_table_id 
    LEFT OUTER JOIN v_catalog.projections p
        ON t.table_id = p.anchor_table_id
        AND p.is_super_projection IS TRUE
GROUP BY
    t.table_schema, t.table_name, p.is_segmented
ORDER BY
    t.table_schema, t.table_name
;

Sample output:

 schema |         table          | row_count | segmented | num_proj 
--------+------------------------+-----------+-----------+----------
 mauro  | city                   |         5 | Yes       |        2
 mauro  | employees              |   1000000 | Yes       |        2
 mauro  | empty                  |         0 | No        |        0
 mauro  | fnames                 |        20 | Yes       |        2
 ...
 tpch   | customer               |         0 | Yes       |        2
 tpch   | lineitem               |  54010935 | Yes       |        2
 tpch   | nation                 |        25 | No        |        1
 tpch   | orders                 | 718277000 | Yes       |        2

I did add a couple of columns: segmented (Yes/No) and num_proj. You can remove them if you want.

mauro
  • 5,730
  • 2
  • 26
  • 25
  • This looks like it's going into the correct direction. I understand that `ps` stands for the `projection_storage` table. What about `p` though? – basickarl Aug 11 '17 at 13:13
  • I tried the script and it half works. The thing is, some of my tables don't have a projection yet. I have 62 tables, unfortunately `projection_storage` only shows 43 of the 62 tables. This is another way of doing what you did: `select anchor_table_schema, anchor_table_name, row_count from projection_storage order by anchor_table_schema asc, anchor_table_name asc;`. – basickarl Aug 11 '17 at 13:16
  • Hmmm.. ok, will work on it (tables without projections) later this evening. your "alternative" approach won't work on multi-node system with segmented tables... – mauro Aug 11 '17 at 13:44