April 8, 2011 at 7:59 am
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'
April 8, 2011 at 8:27 am
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/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply