title: Count the number of non-null values in each column of each table in MySQL
body: <p>Is there a way to produce this output using SQL for all tables in a given database (using MySQL) without having to specify individual table names and columns?</p>
  
  <pre><code>Table   Column  Count
  ----    ----    ----
  Table1  Col1    0
  Table1  Col2    100
  Table1  Col3    0
  Table1  Col4    67
  Table1  Col5    0
  Table2  Col1    30
  Table2  Col2    0
  Table2  Col3    2
  ... ... ...
  </code></pre>
  
  <p>The purpose is to identify columns for analysis based on how much data they contain (a significant number of columns are empty).</p>
  
  <p>The 'workaround' solution using python (one table at a time):</p>
  
  <pre><code>    # Libraries
      import pymysql
      import pandas as pd
      import pymysql.cursors
  
      # Connect to mariaDB
      connection = pymysql.connect(host='localhost',
                                   user='root',
                                   password='my_password',
                                   db='my_database',
                                   charset='latin1',
                                   cursorclass=pymysql.cursors.DictCursor)
  
  
      # Get column metadata
      sql = """SELECT *
                  FROM `INFORMATION_SCHEMA`.`COLUMNS` 
                  WHERE `TABLE_SCHEMA`='my_database' 
              """
      with connection.cursor() as cursor:
        cursor.execute(sql)
        result = cursor.fetchall()
  
      # Store in dataframe
      df = pd.DataFrame(result)
      df = df[['TABLE_NAME', 'COLUMN_NAME']]
  
      # Build SQL string (one table at a time for now)
      my_table = 'my_table'
      df_my_table = df[df.TABLE_NAME==my_table].copy()
      cols = list(df_my_table.COLUMN_NAME)
      col_strings = [''.join(['COUNT(', x, ') AS ', x, ', ']) for x in cols]
      col_strings[-1] = col_strings[-1].replace(',','')
      sql = ''.join(['SELECT '] + col_strings + ['FROM ', my_table])
  
      # Execute
      with connection.cursor() as cursor:
          cursor.execute(sql)
          result = cursor.fetchall()
  </code></pre>
  
  <p>The result is a dictionary of column names and counts.</p>