Dynamic update statement with variable column names

Question

We're looking to do an update in several SQL Server databases to change all NULL values in a certain table to be empty strings instead of NULL. We're potentially going to be doing this across hundreds of databases. The table name will always be the same, but the column names are variable based on how the front-end application is configured (don't judge... I didn't create this system).

Is there a way to do an update on all of these columns without knowing the column names ahead of time?

Answer 1

You can pass the name of the column in dynamic sql:

declare @sql nvarchar (1000);
set @sql = N'update table set ' + @column_name + '= ''''';

exec sp_executesql @sql;

Answer 2

You can look in the sys.columns table and join on the table name or object_id.

 DECLARE @OBJ_ID INT

 SELECT @OBJ_ID = OBJECT_ID
 FROM SYS.tables
 WHERE name = 'YOURTABLE'

 SELECT * FROM SYS.columns
 WHERE OBJECT_ID = @OBJ_ID

You could use the name field from the sys.columns query as a basis to perform the update on.