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?
可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效,请关闭广告屏蔽插件后再试):
问题:
回答1:
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)
回答2:
EXEC sp_MSForEachDB ' Use ?; UPDATE ?.dbo.MyTable SET MyValue=999 '
回答3:
There is an undocumented stored procedure sp_MSForEachDB which will execute SQL for each database.
EXEC sp_msforeachdb 'PRINT ''?'''
The ? is the database name.