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
-- 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.