#!/bin/bash

# This script will count the number of records in all tables  
# in all VAR_DATABASE_NAME* (note the wildcard at the end)
# with the result in just one line
# in CSV-compatible layout

## one-liner
## databases=( `ls /var/lib/mysql/ `) ;for d in "${databases[@]}" ; do tables=( `mysql -e "use $d ; show tables"` ) ; for t in "${tables[@]}"  ; do if [[ "$d" == ""* ]]  ; then   mysql -s -r -e "SELECT CONCAT('$d',',','$t',',', count(*)) from $d.$t  " 2> /dev/null | grep -v CONCAT >> all.csv ; fi ; done ; done ;


## sample result : 
## my_table_name,74883

VAR_DATABASE_NAME=mydb_

databases=( `ls /var/lib/mysql/ `)

for d in "${databases[@]}" ; do 

  if [[ "$d" == "$VAR_DATABASE_NAME"* ]]  ; then

    for d in "${databases[@]}" 
    do 

      tables=( `mysql -e "use $d ; show tables"` ) 

      for t in "${tables[@]}"  
      do
   
        # the CONCAT() function gather the results in one line
        # then we exclude the CONCAT statement itself from the output using grep -v

        mysql -s -r -e "SELECT CONCAT('$d',',','$t',',', count(*)) from $d.$t  " 2> /dev/null | grep -v CONCAT >> all.csv 

      done

  fi

done