HOWTO get a list of all PostgreSQL tables that have data

A $SHELL one-liner to get all the tables with at least one row of data in a PostgreSQL database.

for table_name in `psql -h<DB_HOST> -U<DB_USER> <DATABASE_NAME> -c "\d"|grep table|awk '{print $3}'`; do echo -n $table_name >> tables_with_data.txt; psql -h<DB_HOST> -U<DB_USER> <DATABASE_NAME> -c "SELECT COUNT(*) FROM $table_name" | grep -B 1 "(1 row)"|grep -v row >> tables_with_data.txt; done

You can then filter out lines in the text file that end with a '0' i.e. that table has a COUNT(*) of 0

```bash
#!/bin/bash

DB_HOST=localhost
DB_USER=mydbuser
DATABASE_NAME=mydb

for table_name in `psql -h$DB_HOST -U$DB_USER $DATABASE_NAME -c "\d"|grep table|awk '{print $3}'`; do echo -n $table_name >> tables_with_data.txt; psql -h$DB_HOST -U$DB_USER $DATABASE_NAME -c "SELECT COUNT(*) FROM $table_name" | grep -B 1 "(1 row)"|grep -v row >> tables_with_data.txt; done


awk '!/ 0$/' tables_with_data.txt > tables_with_data && mv tables_with_data tables_with_data.txt
```

ekiara/howto-get-a-list-of-all-postgresql-tables-that-have-data.rst

HOWTO get a list of all PostgreSQL tables that have data