Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

4 Answers 4

up vote 2 down vote accepted

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

share|improve this answer

You can use Aaron's version of sp_MSforeachdb which is more reliable than the native one.

Example :

EXEC sp_foreachdb
       @command = N'ALTER TABLE table_name ADD C1 int;',
       @user_only = 1 -- only user databases
share|improve this answer

The lazy way: Use sp_MSForEachDB.

The safer way: Use a cursor and dynamic SQL.

DECLARE @db sysname
DECLARE @sql nvarchar(max)

--Modify this query to specify which databases you want.
DECLARE db CURSOR FOR
SELECT name FROM master.sys.databases

OPEN db
FETCH NEXT FROM db INTO @db

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'ALTER TABLE ' + QUOTENAME(@db) + '.dbo.sometable ADD somecolumn varchar(50)'
    EXEC (@sql)
    FETCH NEXT FROM db INTO @db
END

CLOSE db
DEALLOCATE db
share|improve this answer

You can create a script to add a column for a table. If all of the tables are identical it is not too hard.

  1. Create the ALTER TABLE script.
  2. Run that script in every database where the table exists.

That is all there is to it. You can, of course, automate the distribution across several dataases on a server or even several servers. Depending on your landscape the details are different. Assuming that all databases are on a single server it could be:

Companies like Red Gate, Apex, etc have tools to help you automate this if you choose to acquire a tool.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.