2

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 <table> SET <column_name> = 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?

3

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'

  • No worries, just be careful, that script can cause a lot of damage if you're not. – Rich Benner Jun 30 '16 at 14:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.