title: Return column names and count of their distinct values in MySQL
body: <p>I am looking for a dynamic MySQL query which would count the distinct values in each column of a table and which would also tell me which of the columns contain Null values.<br>
  Here is the sample table, I used <em>'db_name'</em> as database name and <em>'table_name'</em> as table name:</p>
  
  <pre><code>+------+------+------+------+------+
  | Col1 | Col2 | Col3 | Col4 | Col5 |
  +------+------+------+------+------+
  | a    | d    | j    | o    | q    |
  | b    | e    | k    | o    | r    |
  | c    | f    | l    | o    | NULL |
  | a    | g    | NULL | p    | t    |
  | b    | h    | m    | NULL | r    |
  | a    | i    | n    | p    | s    |
  +------+------+------+------+------+
  </code></pre>
  
  <p>This is the result set that I would like to get:</p>
  
  <pre><code>+----------+---------------+---------------+
  | Col Name | Unique values | Contains Null |
  +----------+---------------+---------------+
  | Col1     |             3 | FALSE         |
  | Col2     |             6 | FALSE         |
  | Col3     |             6 | TRUE          |
  | Col4     |             3 | TRUE          |
  | Col5     |             5 | TRUE          |
  +----------+---------------+---------------+
  </code></pre>
  
  <p>So far this is what I have managed to figure out:</p>
  
  <pre class="lang-sql prettyprint-override"><code>-- list all the column names
  
  SELECT COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'table_name';
  
  -- count the distinct values in a column
  
  SELECT COUNT(DISTINCT Col1) Col1 
  FROM table_name;
  
  -- tell if a column contains any Null
  
  SELECT
    (CASE WHEN (SUM(CASE WHEN Col1 IS NULL THEN 1 ELSE 0 END)) &gt; 0 THEN 'TRUE' 
    ELSE 'FALSE' END) 'Contains Null'
  FROM table_name;
  
  -- combining the queries
  
  SELECT
    (SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'table_name' AND COLUMN_NAME = 'Col1') 'Col Name', 
    (SELECT COUNT(DISTINCT Col1) 
    FROM table_name) 'Unique values',
    (SELECT (CASE WHEN (SUM(CASE WHEN Col1 IS 
    NULL THEN 1 ELSE 0 END)) &gt; 0 THEN 'TRUE' ELSE 'FALSE' END)
    FROM table_name) 'Contains Null';
  </code></pre>
  
  <p>Now, I assume I would need to build a loop that goes through each column and unifies the records returned by the query or inserts them into a new table. The problem is, I am relatively new to SQL and I am not really familiar with loops and variables yet.</p>
  
  <p>I found a few questions similar to mine, but none of them gave me a clear answer:</p>
  
  <p><a href="https://stackoverflow.com/questions/12625729/sql-server-count-number-of-distinct-values-in-each-column-of-a-table">SQL Server count number of distinct values in each column of a table</a></p>
  
  <p><a href="https://stackoverflow.com/questions/34319753/return-column-name-and-distinct-values">Return column name and distinct values</a></p>
  
  <p><a href="https://stackoverflow.com/questions/1363576/sql-count-number-of-distinct-values-in-every-column">SQL: count number of distinct values in every column</a></p>