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

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