CODEWITHSUNDEEP
sqlfor-loopsql-updatedynamic-sql
I am not Fat
I am not Fat

Reputation: 447

Looping through table and column combinations

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'?

Upvotes: 0

Views: 197

Answers (1)

Farshid Shekari
Farshid Shekari

Reputation: 2449

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)

Upvotes: 1

Related Questions