Hi, I need to update a certain Table's Column in all user database (no Master and Reporting databases) in SQL Server. The value set in the Column is for all databases the same. Basically the query (or SP) would do something simple like this, but automatically over all database.
Update <database>.dbo.<Tablename> SET <Column> = 'my value'
Thank you both for the supplied solution. @Andrei Fomitchev Your solution works, but I had to correct some parts of the sql. The System databases aren't need so I've added "WHERE database_id > 4 " Adding the Where clause during the actual update isn't needed in my situation as the updated table always has only one record in it and always must be updated. Updated version that works:
DECLARE @sql nvarchar(1000);DECLARE @dbName sysname;DECLARE @tbName sysname;DECLARE @clName sysname;SET @tbName = 'DBversie';SET @clName = 'ESversie';DECLARE crDB CURSOR FOR SELECT name FROM sys.databases WHERE database_id > 4 ORDER BY name; -- NO System databasesOPEN crDB;WHILE 1=1 BEGIN FETCH NEXT FROM crDB INTO @dbName; IF @@FETCH_STATUS <> 0 BREAK-- ... SET @sql = 'UPDATE ['+@dbName+'].[dbo].['+@tbName+'] SET ['+@clName+']=''1.2.0.0'' ' EXEC (@sql) -- For debug replace it with PRINT @sql to watch what will be executed.-- ...END;CLOSE crDB;DEALLOCATE crDB;
@pcelba This solution is very easy to implement en easy to understand. I ended up using "sp_ineachdb" from First Responder Kit as the "sp_msForEachDB" doesn't handle database with a space in their name (and has many other limitations which by the way I don't need for this purpose). Based on these remarks I gave my rating.
DECLARE @sql nvarchar(1000)SET @sql = 'USE ?;IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''DBversie'') UPDATE dbo.DBversie SET ESversie = ''my value'''EXEC sp_inEachDB @sql
@Andrei Fomitchev Your solution works, but I had to correct some parts of the sql. The System databases aren't need so I've added "WHERE database_id > 4 "
Adding the Where clause during the actual update isn't needed in my situation as the updated table always has only one record in it and always must be updated.
Updated version that works:
Open in new window
@pcelba This solution is very easy to implement en easy to understand. I ended up using "sp_ineachdb" from First Responder Kit as the "sp_msForEachDB" doesn't handle database with a space in their name (and has many other limitations which by the way I don't need for this purpose).Based on these remarks I gave my rating.
Open in new window