Summary of default values in MariaDB database

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


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;


  • 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)


  • 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

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.