Avatar of Stef Merlijn
Stef Merlijn
Flag for Netherlands asked on

Update column in all databases

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'

Open in new window

How can this be done?
Microsoft SQL Server

Avatar of undefined
Last Comment
Pavel Celba

8/22/2022 - Mon
SOLUTION
Andrei Fomitchev

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Pavel Celba

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Stef Merlijn

ASKER
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 databases
OPEN 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;

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.
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

Open in new window

Pavel Celba

If your database contains spaces in name then you may simply enclose it into brackets and it should work in sp_msForEachDB:
SET @sql = 'USE [?];IF EXISTS (SELECT * FROM ....

Open in new window

OTOH, Ozar's tools are great.

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck