Add a new column to every table in a database

ByDr. SubraMANI Paramasivam

Add a new column to every table in a database

This seems quite a painful job when so many start opening a table and add the new column name, datatype and save. But what about you have to do this for 200 tables in a database and say you have 10 databases. Hmmm…. Such a crap work doing manually but SQL Server infamiliar stored procedure comes to rescue at this point.

The below query can be used to add a new column to every single table in a database.

Use [db]
go
exec sp_MsForEachTable ‘alter table ? add Load_date datetime null’

Thats it. You are done. Change the database name for the other databases.

So it is not just adding a column to all table, but modifying anything similar for each table in a database. You can change the SQL statement within the single quotes.

Best of luck

About the Author

Dr. SubraMANI Paramasivam administrator

Dr.SubraMANI Paramasivam is a Data Platform MVP, Power BI Group Leader, International Speaker, Microsoft Certified Trainer and Principal Solutions Architect at DAGEOP Ltd, having 20+ years of DB, BI & Analytics experience. Dr.SubraMANI Paramasivam is a frequent speaker in SQLBITS, PASS Summit, PASS SQL Saturdays, SQLRelay, Data Platform Summit, MCT Summit, SQLServerGeeks Summit, INSIDESQL UK, Data Awareness Programmes, Data Day Events. He likes to enlighten the education & data and started his own journey with FREE Data Awareness Programme (www.dataap.org), in remote areas who are in need. Mani speaks a lot about Visualization, Data Science, R Analytics, Azure concepts, Performance Tuning, DB & SQL Server Architecture, DBA, Partitioning, Resource Governor, Disaster Recovery, High Availability, Development, T-SQL Programming and Microsoft certifications.

Leave a Reply