C# / .NETDevOpsMisc
C# / .NET
SQL Batch alter all columns
Alexandru Puiu
Alexandru Puiu
September 16, 2012
1 min

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'

Tags

utils
Alexandru Puiu

Alexandru Puiu

Engineer / Security Architect

Systems Engineering advocate, Software Engineer, Security Architect / Researcher, SQL/NoSQL DBA, and Certified Scrum Master with a passion for Distributed Systems, AI and IoT..

Expertise

.NET
RavenDB
Kubernetes

Social Media

githubtwitterwebsite

Related Posts

RavenDB Integration Testing
Using RavenDB in Integration Testing
December 24, 2022
2 min

Subscribe To My Newsletter

I'll only send worthwhile content I think you'll want, less than once a month, and promise to never spam or sell your information!
© 2022, All Rights Reserved.

Quick Links

Get In TouchAbout Me

Social Media