Skip to content

Instantly share code, notes, and snippets.

@brijrajsingh
Created August 23, 2016 07:36
Show Gist options
  • Save brijrajsingh/efd3c273440dfebcb99a62119af2ecd5 to your computer and use it in GitHub Desktop.
Save brijrajsingh/efd3c273440dfebcb99a62119af2ecd5 to your computer and use it in GitHub Desktop.
Convert all the NOT NULL Columns in a Mysql database to Default NULL
SELECT CONCAT_WS('.',TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME) col,CONCAT('alter table ',TABLE_NAME,' MODIFY COLUMN ', COLUMN_NAME,' ',DATA_TYPE,'(',CHARACTER_MAXIMUM_LENGTH,') NULL DEFAULT NULL') as script_col
FROM information_schema.COLUMNS
WHERE is_nullable=0
and length(COLUMN_DEFAULT) is NULL and
CHARACTER_MAXIMUM_LENGTH is not NULL and
table_schema = 'dbName'
Union
SELECT CONCAT_WS('.',TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME) col,CONCAT('alter table ',TABLE_NAME,' MODIFY COLUMN ', COLUMN_NAME,' ',DATA_TYPE,' NULL DEFAULT NULL') as script_col
FROM information_schema.COLUMNS
WHERE is_nullable=0
and length(COLUMN_DEFAULT) is NULL and
CHARACTER_MAXIMUM_LENGTH is NULL and
table_schema = 'dbName'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment