Looping through databases on the server and updating data

Question

I have a server with several databases. I need to go through these databases and change the value in one record in one table in each database. How can I do that?

Answer 1

You can use dynamic SQL:

declare @query varchar(max)
set @query = ''

select  @query = @query + 'UPDATE ' + name + 
            '.dbo.YourTable set value = 1 where id = 2; '
from    master.sys.databases
where   name <> 'master'

exec (@query)

Answer 2

EXEC sp_MSForEachDB ' Use ?; UPDATE ?.dbo.MyTable SET MyValue=999  '

Answer 3

There is an undocumented stored procedure sp_MSForEachDB that will execute SQL for each database.

EXEC sp_msforeachdb 'PRINT ''?'''

What? This is the name of the database.