Friday, March 20, 2015

Postgres -- Tables and Column Length

To generate the following outoput in PostgresSQl


ID Table Name Schema Count of Columns TotalRows
1 Test1 public 4 600
2 Test2 public 12 1268

The following code works well:

SELECT
  t.table_name, t.table_schema, count(columns.column_name), n_live_tup AS totalRowCount  FROM
  information_schema.tables t
  LEFT OUTER JOIN  information_schema.columns on  columns.table_name = t.table_name
  LEFT OUTER JOIN  pg_catalog.pg_stat_user_tables pc on t.table_name = pc.relname
  WHERE t.table_schema ='public'
GROUP BY t.table_name, t.table_schema, n_live_tup


No comments:

Post a Comment