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?
Loop through databases on server, and update data
Asked Answered
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)
Thanks Andomar. I think this is more suited to my situation than the 'sp_MSForEachDB' stored procedure as I may need to be more custom with each database. Using this method I could create a loop, and various IF conditions to get the results I require. Thanks. –
Rentier
EXEC sp_MSForEachDB ' Use ?; UPDATE ?.dbo.MyTable SET MyValue=999 '
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)
Thanks Andomar. I think this is more suited to my situation than the 'sp_MSForEachDB' stored procedure as I may need to be more custom with each database. Using this method I could create a loop, and various IF conditions to get the results I require. Thanks. –
Rentier
There is an undocumented stored procedure sp_MSForEachDB which will execute SQL for each database.
EXEC sp_msforeachdb 'PRINT ''?'''
The ? is the database name.
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.
© 2022 - 2024 — McMap. All rights reserved.