Quickly Add and Drop a Column from all Tables in SQL Server

If you have been looking out for a quick way to add and drop a column from all the tables in SQL Server, then here’s the query for it. I will use an undocumented stored procedure sp_MSforeachtable for this purpose

USE Northwind
GO
-- Add a Column to all tables
EXEC sp_MSforeachtable 'ALTER TABLE ? ADD ColumnTemp varchar(10) NULL'

-- Remove a Column from all tables
EXEC sp_MSforeachtable 'ALTER TABLE ? DROP COLUMN ColumnTemp'

The query shown above adds the column ‘ColumnTemp’ to all tables in the Northwind database. Similar the ‘ColumnTemp’ is then removed from all the tables.

Note: While removing a column, I am assuming there are no constraints defined.