CODEWITHSUNDEEP
mysql
Steve
Steve

Reputation: 1636

Alter All TinyInt(1) Columns In Database to Not Null Default 0

I have a database, and I need to alter every single 

tinyint(1) default null

column (and incidentally, every value in those columns set to null) in every single table in that database to:

tinyint(1) not null default 0

as soon as possible.

What is the fastest / most efficient way to achieve this, (programmatically or otherwise)?

Upvotes: 2

Views: 6857

Answers (1)

Mchl
Mchl

Reputation: 62359

Query information_schema.COLUMNS view for a list of tables/columns, then use your programming language of choice to loop through the results and perform ALTER TABLE queries.

SELECT TABLE_NAME, COLUMN_NAME 
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'yourDatabase'
AND COLUMN_TYPE = 'tinyint(1)'

Upvotes: 1

Related Questions