List table default values in MySQL database

The query below lists the table default values.

Query

select table_schema as database_name,
       table_name,
       column_name,
       column_default
from information_schema.columns
where  column_default is not null
      and table_schema not in ('information_schema', 'sys',
                               'performance_schema','mysql')
--    and table_schema = 'your database name'
order by table_schema,
         table_name,
         ordinal_position;

Columns

  • database_name - name of the database (schema)
  • table_name - name of the table
  • column_name - name of the column
  • column_default - SQL expression that defines this column default

Rows

  • One row: represents one column with defined default value
  • Scope of rows: all columns with default values in the database (schema)
  • Ordered by: database name, table name and column position in table

Sample results

0
There are no comments. Click here to write the first comment.