Query below summarizes default constraints in a database listing all distinct default value definitions for columns with the number of their occurrence.

Query

select column_default,
       count(distinct concat(col.table_schema, '.', col.table_name)) as tables,
       count(column_name) as columns
from information_schema.columns col
join information_schema.tables tab on col.table_schema = tab.table_schema
                                   and col.table_name = tab.table_name
where col.table_schema not in ('sys', 'information_schema',
                               'mysql', 'performance_schema')
      and tab.table_type = 'BASE TABLE'
group by column_default
order by tables desc,
         columns desc;

Columns

• column_default - definition of default constraint (formula) and NULL for columns without a default value
• tables - number of tables with this constraint (or number of tables with columns with no constraints for NULL row)
• columns - number of columns with this particular constraint (or no constraint for NULL row)

Rows

• One row represents one unique definition of default value
• Scope of rows: all distinct definitions of default column values in a database and one NULL row for columns without constraints
• Ordered by the number of occurrences from the most popular

Sample results