Keeping the values in a development database in sync while multiple developers are changing the database structure can sometimes be a bit of a challenge.

For example, recently we needed to ensure that all emails in all database tables are replaced with fresh values. It is not a problem when working with a single table, for example aspnet_Membership. It is much more interesting when you have ten or even more tables containing the email field, they are frequently changed and you need to be sure you covered all those tables. That’s the case when you might need an alternative, but still simple solution.

Implementation

The basic idea is to get all table fields containing the keyword ‘mail’ in their name, have “string-based” field types(char, nchar, nvarchar…) and save it to the temporary table.

INSERT INTO #column(columnCounter, objectId, schemaName, tableName, columnName)
SELECT ROW_NUMBER() OVER(ORDER BY C.name), O.object_id, S.NAME, O.name, C.name
FROM   sys.columns C
    INNER JOIN sys.types T ON C.SYSTEM_TYPE_ID = T.SYSTEM_TYPE_ID
    INNER JOIN sys.objects O ON C.object_id = O.object_id
    INNER JOIN sys.schemas S ON O.schema_id = S.schema_id
WHERE T.Name IN ('char','nchar','nvarchar','varchar','text','ntext') AND O.type = 'u'
    AND  (C.name  LIKE('%MAIL'))

Our next step is to navigate through all those columns and update their values with the new data.

WHILE @counter <= @columnsCount
BEGIN
    DECLARE @currentTableName NVARCHAR(100)
    DECLARE @currentColumnName NVARCHAR(100)
    SET @currentTableName = (SELECT tableName FROM #column WHERE columnCounter = @counter)
    SET @currentColumnName = (SELECT columnName FROM #column WHERE columnCounter = @counter)

    SET @sqlCommand = 'UPDATE ' + @currentTableName + ' SET ' + @currentColumnName + ' = ' + '''' + @newEmailValue + ''''
    EXEC(@sqlCommand)
    print @sqlcommand
    PRINT Convert(varchar(10), @@ROWCOUNT ) + ' row(s) affected in ' + @currentTableName + '.' + @currentColumnName
    SET @counter = @counter + 1
END

It is strongly recommended to make a backup of your database because all email fields will be replaced with the data you specify in your script. I’ve tested it on the database with 300 + tables and the execution time was quite fast, about 3 seconds. Please find a complete script attached to this post.

Download attachment