update all DB

Question

I want to update the cells with the value "ABCD" to "XYZW" in all tables in the DB.

Answer 1

Hi @ahmet sincar

First you need to generate a list of all tables and their columns.

SELECT TABLE_NAME,COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'YourDatabaseName'

Then write a dynamic query to generate SQL statements to update the desired value.

Here is a sample using cursor:

DECLARE @TableName NVARCHAR(MAX)
DECLARE @ColumnName NVARCHAR(MAX) 

DECLARE cur CURSOR FOR 
SELECT TABLE_NAME,COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'YourDatabaseName'
 
OPEN cur
FETCH NEXT FROM cur INTO @TableName, @ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = 'UPDATE ' + QUOTENAME(@TableName) + ' SET ' + QUOTENAME(@ColumnName) + ' = REPLACE('+ QUOTENAME(@ColumnName)+ ',''ABCD'',''XYZW'')'
    EXEC sp_executesql @SQL
    FETCH NEXT FROM cur INTO @TableName, @ColumnName
END
 
CLOSE cur
DEALLOCATE cur

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

The following code should do exactly what you need. You can also adjust the data types that it should apply to if you want.

DECLARE @StringToReplace nvarchar(max) = N'ABCD';
DECLARE @ReplacementString nvarchar(max) = N'XYZW';

DECLARE @CRLF nchar(2) = NCHAR(13) + NCHAR(10);
DECLARE @INDENT nchar(4) = N'    ';

SET NOCOUNT ON;

DECLARE @Columns TABLE
(
    SchemaName sysname,
    TableName sysname,
    ColumnName sysname,
    TableColumnID int
);

INSERT @Columns (SchemaName, TableName, ColumnName, TableColumnID)
SELECT s.[name] AS SchemaName,
       t.[name] AS TableName,
       c.[name] AS ColumnName,
       ROW_NUMBER() OVER(PARTITION BY s.[name], t.[name] ORDER BY c.column_id) 
         AS TableColumnID
FROM sys.columns AS c
INNER JOIN sys.tables AS t 
ON t.object_id = c.object_id 
INNER JOIN sys.schemas AS s
ON s.schema_id = t.schema_id 
INNER JOIN sys.types AS typ
ON typ.system_type_id = c.system_type_id 
AND typ.user_type_id = c.system_type_id 
WHERE typ.[name] IN (N'char', N'nchar', N'varchar', N'nvarchar')
AND t.is_ms_shipped = 0
AND t.[name] <> N'sysdiagrams';

DECLARE @Tables TABLE
(
    TableKey int IDENTITY(1, 1) PRIMARY KEY,
    SchemaName sysname,
    TableName sysname
);

INSERT @Tables (SchemaName, TableName)
SELECT DISTINCT SchemaName, TableName 
FROM @Columns;

DECLARE @TableCounter int = 1;
DECLARE @ColumnCounter int;
DECLARE @SchemaName sysname;
DECLARE @TableName sysname;
DECLARE @ColumnName sysname;
DECLARE @MaximumTableKey int = (SELECT MAX(TableKey) FROM @Tables);
DECLARE @MaximumColumnKey int;
DECLARE @SQL nvarchar(max);

WHILE @TableCounter <= @MaximumTableKey
BEGIN
    SELECT @SchemaName = SchemaName,
           @TableName = TableName 
    FROM @Tables 
    WHERE TableKey = @TableCounter;

    SET @SQL = N'UPDATE ' + QUOTENAME(@SchemaName) 
               + N'.' + QUOTENAME(@TableName) + @CRLF 
               + N'SET ' + @CRLF;
    SET @MaximumColumnKey = (SELECT MAX(TableColumnID) FROM @Columns 
                                    WHERE SchemaName = @SchemaName 
                                    AND TableName = @TableName);
    SET @ColumnCounter = 1;
    
    WHILE @ColumnCounter <= @MaximumColumnKey
    BEGIN
        SET @ColumnName = (SELECT ColumnName FROM @Columns 
                                  WHERE SchemaName = @SchemaName 
                                  AND TableName = @TableName
                                  AND TableColumnID = @ColumnCounter);
        SET @SQL += @INDENT + QUOTENAME(@ColumnName) + N' = REPLACE('
                  + QUOTENAME(@ColumnName) + N', N''' + @StringToReplace
                  + N''', N''' + @ReplacementString + N''')'
                  + CASE WHEN @ColumnCounter = @MaximumColumnKey
                         THEN N';'
                         ELSE N','
                    END + @CRLF;
        SET @ColumnCounter += 1;
    END;
    --PRINT @SQL;
	EXEC (@SQL);
    SET @TableCounter += 1;
END;

I've also left a print statement commented out at the bottom so you can get an idea what the generated code looks like.

The code will also match any alias data types that have an underlying data type that's in the list in the code. For example, if PhoneNumber is an nvarchar(20), it would also match without needing to be listed separately.

Hope that helps.