How do I update all the columns named 'ModifiedBy' in all tables of a database-CodePudding

I need to remove all personal data from a database. Many of the tables have a 'ModifiedBy' column which contains users' emails.

Is there a convenient way to iterate over every table in the database which has a ModifiedBy column and run an update such as the one below:

UPDATE [TableName] 
SET [ModifiedBy] = CONVERT (
   nvarchar(max), 
   HASHBYTES('SHA', CONCAT('MY_SALT_VALUE', [ModifiedBy])), 
   2
)

CodePudding user response：

A dynamic statement based on sys.columns, sys.tables and sys.schemas system views is a possible option:

DECLARE @stm nvarchar(max)
DECLARE @err int

SELECT @stm = STRING_AGG(
   CAST(CONCAT(
      N'UPDATE ',
      QUOTENAME(sch.[name]), N'.', QUOTENAME(tab.[name]),
      N' SET ', 
      QUOTENAME(col.[name]), N' = CONVERT (nvarchar(max), HASHBYTES(''SHA'', CONCAT(''MY_SALT_VALUE'', ',
      QUOTENAME(col.[name]), N')), 2)'
   ) AS nvarchar(max)),
   '; '
)
FROM sys.columns col
JOIN sys.tables tab ON col.object_id = tab.object_id
JOIN sys.schemas sch ON tab.schema_id = sch.schema_id
WHERE col.[name] = 'ModifiedBy' 

PRINT @stm
EXEC @err = sp_executesql @stm
IF @err <> 0 PRINT 'Error found'