Alter Table for Multiple Databases (same table name)

Is there a good way to Alter a table (add a column) for multiple databases (think like 50 or so). Or is it pretty much manual for all of them?

the Tables are all the same and the column added will be the same as well.

Thanks!

Here's the technique I frequently use to do things like this with dynamic SQL. This also has applications beyond just "looping" across databases, too.

Now, you didn't say which 50 databases, so I just went with everything in the instance, as that's a pretty common thing to do:

DECLARE @sql nvarchar(MAX);

SELECT
    @sql = ISNULL(@sql, N'') +
        N'ALTER TABLE ' + QUOTENAME(d.name) + N'.[dbo].[MyTable] ADD C1 int;'
    FROM sys.databases d
    WHERE
        (d.database_id > 4) AND /* No system databases */
        (d.state = 0) AND /* Online only */
        (d.is_distributor = 0) AND /* Not a distribution database */
        (d.is_read_only = 0) AND /* We can write to it */
        /* TODO: filter me more? */;

BEGIN TRANSACTION;
    EXEC(@sql);
COMMIT TRANSACTION;

You can replace the sys.databases part with a list of constants, or a query from a master database table, or whatever is appropriate for your scenario. There's also no error checking, so you may want to add that.

(Yes, I left the syntax error in the above code on purpose.)

StackExchange Database Administrators Q#49993, answer score: 2