CODEWITHSUNDEEP
sql-server-2005t-sqlloops
Curtis
Curtis

Reputation: 103358

Loop through databases on server, and update data

I have a server with multiple databases. I need to loop through these databases and change a value in one record, in one table, in each database. How can this be done?

Upvotes: 6

Views: 14240

Answers (4)

Anton Shepelev
Anton Shepelev

Reputation: 1011

You can write a straight-forward loop:

DECLARE @UPD_TMP NVARCHAR(MAX) = 'UPDATE [<db>]..[my_table] SET [f1] = 0'
DECLARE @upd_cmd NVARCHAR(MAX)
DECLARE @db AS NVARCHAR(256)
DECLARE db_c CURSOR STATIC FOR
    SELECT name FROM master.sys.databases
    WHERE LEN(owner_sid)>1

OPEN db_c
WHILE 1 = 1 BEGIN
    FETCH db_c INTO @db
    IF @@FETCH_STATUS <> 0 BREAK
    SET @db = REPLACE(@db, '''', '''''')
    SET @upd_cmd = REPLACE(@UPD_TMP, '<db>', @db)
    PRINT @upd_cmd -- debug
    EXEC sp_executesql @upd_cmd
END
CLOSE db_c
DEALLOCATE db_c

If you need to pass parameters to your script, make sure to do it the right way.

Upvotes: 0

rlb.usa
rlb.usa

Reputation: 15043

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

Upvotes: 13

Chris Diver
Chris Diver

Reputation: 19812

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

EXEC sp_msforeachdb 'PRINT ''?'''

The ? is the database name.

Upvotes: 3

Andomar
Andomar

Reputation: 238076

You could 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)

Upvotes: 8

Related Questions