Search & Replace Database Column Text

To search an replace text in database columns that hold CHAR, VARCHAR, NCHAR and NVARCHAR data types, open a new query window and execute the following line of code:

Code Snippet

CREATE PROCEDURE p_SearchAndReplace
                (@iSearchString VARCHAR(100)
                ,@iReplaceString VARCHAR(100)
                )
AS
BEGIN

    SET NOCOUNT ON;

    -- Declare variables
    DECLARE @lvTableName     VARCHAR(100);
    DECLARE @lvColumnName    VARCHAR(100);
    DECLARE @lvSQL           VARCHAR(2000);
    DECLARE @lvSearchString2 VARCHAR(2000);
    DECLARE @liCounter       INT;
    DECLARE @lvColumnList    VARCHAR(2000);

    -- Initialise variables
    SET @lvTableName     = '';
    SET @lvColumnName    = '';
    SET @lvSQL           = '';
    SET @lvSearchString2 = QUOTENAME('%' + @iSearchString + '%', '''');
    SET @liCounter       = 0;
    SET @lvColumnList    = '';

    -- Declare cursor to get list of tables in the system
    DECLARE curTableList CURSOR FOR
    SELECT  QUOTENAME([table_schema]) + '.' + QUOTENAME([table_name])
    FROM    [information_schema].[tables]
    WHERE   [table_type] = 'BASE TABLE'
    AND     [table_name] LIKE '%<TableName>%';

    -- Open cursor to get list of tables to process
    OPEN  curTableList
    FETCH NEXT FROM curTableList
    INTO  @lvTableName;

    -- Loop until all tables are processed
    WHILE @@FETCH_STATUS = 0
    BEGIN

        -- Initiliase variables
        SET @lvColumnList = '';

        -- Declare cursor to get list of columns for the table
        DECLARE curTableColumnList CURSOR FOR
        SELECT  QUOTENAME([column_name])
        FROM    [information_schema].[columns]
        WHERE   [table_schema] = PARSENAME(@lvTableName, 2)
        AND     [table_name]   = PARSENAME(@lvTableName, 1)
        AND     [data_type]    IN ('CHAR', 'VARCHAR', 'NCHAR', 'NVARCHAR');

        -- Open cursor to get list of tables to process
        OPEN  curTableColumnList
        FETCH NEXT FROM curTableColumnList
        INTO  @lvColumnName;

        -- Loop until all tables are processed
        WHILE @@FETCH_STATUS = 0
        BEGIN

            -- Initiliase variables
            SET @lvSQL = '';

            -- Build the SQL statement to update the table column
            SET @lvSQL = 'UPDATE ' + @lvTableName + ' ' +
                         'SET '    + @lvColumnName + ' = REPLACE(' +
                                     @lvColumnName + ', ' +
                                     QUOTENAME(@iReplaceString, '''') + ') ' +
                         'WHERE '  + @lvColumnName + ' LIKE ' +
                                     @lvSearchString2;

            -- Run the SQL statement
            EXEC (@lvSQL);

            -- Get the number of rows that were affected
            SET @liCounter = @@ROWCOUNT;

            -- If a record was update then get the column that was changed
            IF @liCounter > 0
                BEGIN
                    IF @lvColumnList = ''
                        SET @lvColumnList = @lvColumnName;
                    ELSE
                        SET @lvColumnList = @lvColumnList + ',' + @lvColumnName;
                END;

            -- Get the next column from the list
            FETCH NEXT FROM curTableColumnList
            INTO  @lvColumnName;

        END;

        -- Show counter details
        SELECT 'Replaced ' + CAST(@liCounter AS VARCHAR) +
               ' occurence(s) for table ' + @lvTableName + ' for columns ' +                @lvColumnList AS 'Outcome';

        -- Close and deallocate the cursor for column list
        CLOSE      curTableColumnList;
        DEALLOCATE curTableColumnList;

        -- Get the next table from the list
        FETCH NEXT FROM curTableList
        INTO  @lvTableName;

    END;

    -- Close and deallocate the cursor for table list
    CLOSE      curTableList;
    DEALLOCATE curTableList;

END;