Altering Multiple Tables SQL Server

Question

I have a simple DB that has around 20 different tables.

I want to add a column to every table whose name has "Report Date", and to give it a mm-yyyy value.

I can do it one table at the time by using this query:

USE [RSA] GO ALTER TABLE [dbo].[RSA_BIRMINGHAM_1941$] ADD [month] nvarchar(255);

But it would be easier if I can alter them all at once.

You can pull the table names from sysobjects, and build up a dynamic sql string and execute it. Probably overkill for 20 tables, and if you had 1000 tables then you would need many other strategies for dealing with your db, including getting your design right first time. My point was use the solution that works best - in this case cut/copy/paste :-) — Lee Willis
– Lee Willis, Commented Mar 22, 2016 at 9:34
@RaulGonzales you can use sys.tables for building dynamic query which alters the table definition for you. Important thing to note is that if tables contain data you should create the new column as NULLable. See my answer — DhruvJoshi
– DhruvJoshi, Commented Mar 22, 2016 at 9:42

DhruvJoshi · Accepted Answer · 2016-03-22 09:38:20Z

You can use this dynamic query

DECLARE @q nvarchar(max) DECLARE @c int SELECT @c=count(1) from sys.tables where type='U' -- add any other where condition here WHILE (@c>0) BEGIN select @q = 'ALTER TABLE ['+ t.name + '] ADD [month] nvarchar(255) NULL; ' FROM ( SELECT name, ROW_NUMBER() OVER(ORDER BY name ASC) as r FROM sys.tables where type='U' ) t where t.r=@c SET @c=@c-1 --PRINT(@q) EXEC(@q) END

Explanation: We use sys.tables table which provides us the name of all tables need to be altered.

If you have any business rule like only tables whose name starts with 'Report Date' then you need to modify at two places

--here SELECT @c=count(1) from sys.tables where type='U' and name like 'Report Date%' --... --... -- and here FROM ( SELECT name, ROW_NUMBER() OVER(ORDER BY name ASC) as r FROM sys.tables where type='U' and name like 'Report Date%' ) t where t.r=@c

Collectives™ on Stack Overflow

Altering Multiple Tables SQL Server

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related