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'
@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