CODEWITHSUNDEEP
sqlsql-servert-sql
Raul Gonzales
Raul Gonzales

Reputation: 906

Altering Multiple Tables SQL Server

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.

Upvotes: 3

Views: 3121

Answers (1)

DhruvJoshi
DhruvJoshi

Reputation: 17126

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

Upvotes: 2

Related Questions