< 1 min read
Sometimes you need to make mass changes to a database, and either add or remove a column, or other table or data manipulations. The script below prints SQL statements you can later execute for whatever you need. It loops through each table in your database, and replaces ? with the table name.
EXEC sp_msforeachtable 'PRINT ALTER TABLE ? ADD LastModified DATETIME NOT NULL DEFAULT GETDATE(); ALTER TABLE ? ADD UserID int NULL; ALTER TABLE ? ADD Deleted bit NOT NULL DEFAULT 0;' EXEC sp_msforeachtable 'PRINT Update ? SET SiteID=1'