Looping through table and column combinations

Question

I need to make a script that goes through all tables, and select those columns where name is specified.

I can find the columns where names are specified,

SELECT sys.columns.name AS ColumnName, tables.name AS TableName 
FROM sys.columns 
JOIN sys.tables ON sys.columns.object_id = tables.object_id
                AND sys.columns.name LIKE 'name'

but how do in SQL loop through each row of the select statement and exectute an update command on the table TableName and and column ColumnName, and update the content to something like 'Anon'?

score 1 · Accepted Answer · answered Jan 06 '22 at 16:26

1

I just supposed your searching exact name and don't use Wild cards like %,?,_. You can use the below query:

declare @query varchar(max), @val varchar(100);

set @val ='L';

SELECT @query = COALESCE(@query+ '; ','')+ 'update '+  s.name +'.' + t.name +' set '+ sys.columns.name +' = '''+ @val +''''
FROM sys.columns 
JOIN sys.tables t ON sys.columns.object_id = t.object_id
                AND sys.columns.name LIKE '%Name%'
join sys.schemas s on s.schema_id = t.schema_id


select @query
 
 exec (@query)

answered Jan 06 '22 at 16:26

Farshid Shekari

2,391
4
27
47

The select query seems a bit different in yours? – I am not Fat Jan 06 '22 at 16:29
Yeah, I added a schemas table into the query also because a table can be in another schema. @IamnotFat – Farshid Shekari Jan 06 '22 at 16:32

Looping through table and column combinations

1 Answers1