How can I add the same column to many tables with a query?

Question

The following query is just adding a column to the table.

ALTER TABLE err.PreFinancePaymentPlan 
  ADD PackageName nvarchar(100);

I want to add this column to each table from the following query.

 SELECT TABLE_SCHEMA + '.' + TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_SCHEMA = 'err'

Sample output from the above query.

 err.ColleteralValuablePaper
    err.CustomerPayment
    err.CustomerPaymentItemMatching
    err.DealerColleteralPercent
    err.DealerDistributorStatus
    err.DealerShellLimit
    err.DealerWaitingLimit
    err.DistributorPreFinanceLimit
    err.ColleteralValuablePaper
    .
    .
    .

How can I add this to the entire tablature in one single query?

Answer 1

Think out of the box:

SELECT 'ALTER TABLE ' + TABLE_SCHEMA + '.' + TABLE_NAME
  + ' ADD PackageName nvarchar(100);'
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_SCHEMA = 'err';

Viola! Copy and paste the results of the above SQL and execute.

Ted.

Answer 2

You can use dynamic SQL and a cursor for doing that:

declare @tableName nvarchar(128), @sql nvarchar(max)

declare tableCursor cursor fast_forward for
  SELECT TABLE_SCHEMA + '.' + TABLE_NAME as TableName
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_SCHEMA = 'err'

open tableCursor  
fetch next from tableCursor INTO @tableName
while @@fetch_status=0 begin
  fetch next from tableCursor INTO @tableName
  set @sql='ALTER TABLE '+@tableName+' ADD PackageName nvarchar(100)'
  exec(@sql)
end
close tableCursor;  

This will loop over all the rows returned by the query used in the cursor and execute the dynamic ALTER TABLE statement.

Answer 3

DECLARE @i INT =0;
DECLARE @count INT;
DECLARE @name varchar(50);
DECLARE @sql varchar(200);
SELECT @count = count(TABLE_SCHEMA + '.' + TABLE_NAME)
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_SCHEMA = 'err'
DECLARE @temp TABLE(id int identity(1,1), name varchar(50))
INSERT INTO @temp SELECT (TABLE_SCHEMA + '.' + TABLE_NAME) as name
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_SCHEMA = 'err'

WHILE (@i < @count)
BEGIN

    SELECT @name = name from @temp where id = @i

    SET @sql = 'ALTER TABLE ' + @name + ' ADD PackageName nvarchar(100); ' 
    --print @sql;
    EXEC (@sql)
    SET @name = NULL;
    SET @i = @i+1;
END;