SQL Server – Alter Table for Multiple Databases

alter-tablesql server

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!

Best Answer

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.)}

Related Solutions

Postgresql – Altering same table across multiple databases

First of all, if you'd be using the data type text or varchar (without length modifier) instead of the mostly pointless varchar(n), you wouldn't be in this tight spot to begin with. If you really need to enforce a maximum length, use CHECK constraints for that, those can be altered without messing with the table structure.
More details in this related answer on SO.

Next, you cannot execute SQL command in any other DB than the one you are connected to in standard PostgreSQL at this time. This includes DBs in the same cluster. If you try you get an error like:

ERROR: cross-database references are not implemented: ...

To execute SQL commands in other (Postgres) databases, you could install the additional module dblink. I quote the manual here:

dblink executes a query (usually a SELECT, but it can be any SQL statement that returns rows) in a remote database.

Then you can go about refining your DO statement with dblink calls.

SQL Server – Alter Table and Update in Transactions

You have to break it up into batches because the [Column] does not exist since the statements are being parsed as one batch so it cannot see the new column, hence you receive the error.

Once you break it up into batches with GO statement the column will be added to the table and then your next batch updating the new column will be successful.

Full example:

DROP TABLE #A;
GO
CREATE TABLE #A (ID int IDENTITY(1,1), column1 varchar(1))
GO
INSERT INTO #A (column1)
VALUES('a'),('b'),('c')
GO

begin transaction
alter table #A add [Column] int;
go

begin transaction
update #A set [Column] = 1 where ID=1;
select * from #A;

rollback transaction;
GO

SELECT *
FROM #A

Best Answer

Related Solutions

Postgresql – Altering same table across multiple databases

SQL Server – Alter Table and Update in Transactions

Related Question