Update all tables containing column name In SQL Server, I can find all tables containing a COLUMN_NAME:

SELECT *
FROM   INFORMATION_SCHEMA.columns
WHERE  COLUMN_NAME = 'COLUMN_NAME'


How can I update all of these columns like:

UPDATE &lt;table&gt; SET &lt;column_name&gt; = 123


The solution needs to work in SQL Server 2012 and newer. In my case the column will always be the same datatype: bigint

Do I need to use a CURSOR or similar?
This query will give you a list of your table names and the field name into a temp table.

IF OBJECT_ID('tempdb..#BaseData') IS NOT NULL DROP TABLE #BaseData
GO
CREATE TABLE #BaseData (Schema_Name sysname, Table_Name sysname, Field_Name sysname, SQLScript varchar(max));

DECLARE @FieldName varchar(20); SET @FieldName = 'COLUMN_NAME';

INSERT INTO #BaseData (Schema_Name, Table_Name, Field_Name, SQLScript)
SELECT
s.name
,o.name 
,c.name
,'ALTER TABLE ' + s.name + '.' + o.name + ' SET ' + c.name + ' = ''123'''
FROM sys.columns c
JOIN sys.types t
    ON c.user_type_id = t.user_type_id
JOIN sys.objects o
    ON c.object_id = o.object_id
JOIN sys.schemas s ON o.schema_id=s.schema_id
WHERE c.name LIKE @FieldName 
    AND o.type_desc = 'USER_TABLE';
    SELECT * FROM #BaseData


Results will look like this. The first three columns are not entirely necessary but I've included them to show how the data is put together;

Schema_Name Table_Name  Field_Name    SQLScript
dbo         Table1      Column_Name   ALTER TABLE dbo.Table1 SET Column_Name = '123'
dbo         Table2      Column_Name   ALTER TABLE dbo.Table2 SET Column_Name = '123'
dbo         Table3      Column_Name   ALTER TABLE dbo.Table3 SET Column_Name = '123'
dbo         Table4      Column_Name   ALTER TABLE dbo.Table4 SET Column_Name = '123'


You can then use a cursor to execute each line of your data;

DECLARE @sql VARCHAR(MAX)
DECLARE @SqlScript VARCHAR(max)

DECLARE c CURSOR LOCAL FAST_FORWARD FOR
    SELECT SQLScript
    FROM #BaseData;
OPEN c;
FETCH NEXT FROM c INTO @SQLScript;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = @SqlScript

    EXEC(@sql); 

    FETCH NEXT FROM c INTO @SQLScript;
END

CLOSE c;
DEALLOCATE c;


This will execute each line of code that you've previously created. The example code above will update all tables so that the value in Column_Name = '123'