How to add column to all tables in MySQL Database - MySQL Developer Tutorial

How to add column to all tables in MySQL Database

Let's say that you are working as MySQL Developer and you need to provide script to add a new column to all tables in MySQL Database. 

You can use system tables to get the list of table names, we can generate the DDL statement to add column to all the tables in MySQL Database. Think about that I want to add "CreatedDate" column of datetime to all the tables in "TechBrothers" database. I can use below script. You can make changes according to your database name and column with data type for @DatabaseName and @ColumnName variable.


SET @DatabaseName ="TechBrothers";
SET @ColumnName= " CreatedDate datetime";
SELECT Concat('ALTER TABLE  ', TABLE_NAME,' Add',@ColumnName,';') as Query
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = @DatabaseName;



How to add column to all tables in MySQL Database - MySQL Tutorial for beginners

3 comments: