Alter a table on all databases on the server

• joshua.gonzalez14 63245

  SSC Rookie

  Points: 45

  More actions

  April 8, 2011 at 7:59 am

  #234423

  Hi,

  I am working on a server that has NTH number of databases. Each of these databases have the same table. I would like to run this statement below on all databases.

  IF col_length('redemptionItem','pinVariableName') is null

  BEGIN

  ALTER TABLE [dbo].[redemptionItem]

  ADD pinVariableName nvarchar(100) NULL

  END

  GO

  I know if you use sp_msforeachdb you can loop over all databases. How would I apply the following snippet below and still use the If statement to prevent any errors?

  EXEC sp_MSforeachdb 'ALTER TABLE [dbo].[redemptionItem] ADD pinVariableName nvarchar(100) NULL'

• Adi Cohn-120898

  SSC-Dedicated

  Points: 33944

  More actions

  April 8, 2011 at 8:27 am

  #1309209

  When you use the procedure sp_msforeachdb, it loops through the databases, but it is still being run from the database that you are connected to, so it will run you alter table statement on the same table in the same database each time. In order to make it run each time on a different server, you have to add USE ? before the alter table statement. The procedure will replace the question mark with the database's name each time that it runs so the alter table statement will run on each database. I also recommend to use an if statement to check that the table exists and the column doesn't exist, so you won't get an error message (but even if you won't do it, and you'll get error message for some of the databases, it will work for the rest of the databases).

  Adi

  --------------------------------------------------------------
  To know how to ask questions and increase the chances of getting asnwers:
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/