Reputation: 1842

Find Replace All String Data in a SQL Server Database

I am looking for a script which finds and replaces all fields of type string within a DB with specified text.

The script would for example take the following parameters:

Search for: null
Replace with: empty-string

The primary string data types in SQL Server: Varchar, NVarchar, Text.

This script would then comb through all string based table data and look for in this case null and replace it with a empty string.

Ok I've put together the following code in the meantime.

-- Specify 'dbo' for all tables
DECLARE @schemaName VARCHAR(5) = 'dbo'

BEGIN   
    DECLARE @tableName VARCHAR(255) -- table name   
    DECLARE @tableID INT -- table id (aka syst.table.object_id)   
    DECLARE table_cursor CURSOR FOR 
        SELECT T.object_id AS TableID, T.name AS TableName FROM sys.tables T
        INNER JOIN sys.schemas S ON S.schema_id = T.schema_id
        WHERE S.name = @schemaName
    OPEN table_cursor   
    FETCH NEXT FROM table_cursor INTO @tableID, @tableName  

    WHILE @@FETCH_STATUS = 0   
    BEGIN              
       -- construct each tables queries                 
        DECLARE @totalColumnsFound INT = (SELECT COUNT(*) FROM sys.columns C WHERE OBJECT_ID = @tableID 
        -- text and nvarchar column data types chosen for me (if you need more like ntext, varcahr see sys.types for their ids) 
        AND (C.system_type_id = 35 OR c.system_type_id = 231))
        IF (@totalColumnsFound > 0) 
        BEGIN
            DECLARE @tableUpdateQuery VARCHAR(MAX) = 'update ' + @schemaName + '.' + @tableName + ' set ';
            DECLARE @columnName VARCHAR(255) -- column name   
            DECLARE column_cursor CURSOR FOR 
                SELECT C.name AS ColumnName FROM sys.columns C WHERE OBJECT_ID = @tableID 
                -- text and nvarchar column data types chosen for me (if you need more like ntext, varcahr see sys.types for their ids) 
                AND (C.system_type_id = 35 OR c.system_type_id = 231) 
                OPEN column_cursor   
                FETCH NEXT FROM column_cursor INTO @columnName  

            WHILE @@FETCH_STATUS = 0   
            BEGIN      
               -- construct the columns for the update query, piece by piece.  
               -- This is also where you can apply your logic for how to handle the string update. 
               -- I am trimming string and updating nulls to empty strings here.
               SET @tableUpdateQuery = @tableUpdateQuery + ' ' + @columnName + ' = ltrim(rtrim(isnull(' + @columnName + ',''''))),'                
               FETCH NEXT FROM column_cursor INTO @columnName 
            END  

            CLOSE column_cursor   
            DEALLOCATE column_cursor

            -- trim last comma from string
            SET @tableUpdateQuery = LEFT(@tableUpdateQuery, LEN(@tableUpdateQuery) - 1)

            /** debuging purposes **
            print 'Updating table --> ' + @tableName
            print @tableUpdateQuery
            print ' '
            */

            -- execute dynamic sql
            EXEC(@tableUpdateQuery)                                 
        END    

       FETCH NEXT FROM table_cursor INTO @tableID, @tableName 
    END  

    CLOSE table_cursor   
    DEALLOCATE table_cursor 

END
--GO

Upvotes: 1

Answers (3)

XYZ

Reputation: 39

Here is another answer, similar to above (and hopefully more readable/efficient), since I recently had a similar requirement and this is how I solved it.

CREATE OR ALTER PROCEDURE UPDATE_ALL_COLUMNS
    @TableNameSearchFilter NVARCHAR(100),
    @TableSchema NVARCHAR(100),
    @TestValue NVARCHAR(100),
    @NewValue NVARCHAR(100)
AS
BEGIN
    
    DECLARE @NRCOLUMNS INT;
    DECLARE @i INT = 0;
    DECLARE @COLUMN NVARCHAR(100) = '';
    DECLARE @SQL NVARCHAR(MAX) = '';
    DECLARE @TableToUpdate NVARCHAR(256) = '';
    DECLARE @insertingNULL BIT;

    IF (@NewValue IS NULL) SET @insertingNULL = 1
    ELSE SET @insertingNULL = 0;

    WHILE @TableToUpdate IS NOT NULL
    BEGIN
        SELECT @TableToUpdate = MIN(TABLE_NAME)
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_NAME LIKE @TableNameSearchFilter
            AND TABLE_SCHEMA = @TableSchema
            AND TABLE_NAME > @TableToUpdate;
            
        WITH CTE1 AS
        (
            SELECT ROW_NUMBER() OVER (ORDER BY ORDINAL_POSITION) AS RN
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME = @TableToUpdate
                AND TABLE_SCHEMA = @TableSchema                     
                AND (@insertingNULL = 0 OR (@insertingNULL = 1 AND IS_NULLABLE = 'YES'))
        )
        SELECT @i = MIN(RN), @NRCOLUMNS = MAX(RN) FROM CTE1;

        WHILE (@i <= @NRCOLUMNS AND @TableToUpdate IS NOT NULL)
        BEGIN
            WITH CTE AS
            (
                SELECT *, ROW_NUMBER() OVER (ORDER BY ORDINAL_POSITION) AS RN
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_NAME = @TableToUpdate
                    AND TABLE_SCHEMA = @TableSchema                     
                    AND (@insertingNULL = 0 OR (@insertingNULL = 1 AND IS_NULLABLE = 'YES'))
            )
            SELECT @COLUMN = COLUMN_NAME 
            FROM CTE
            WHERE RN = @i;

            SET @SQL = @SQL + 
                N'UPDATE D SET ' + @COLUMN + N' = ' + ISNULL(N'''' + @NewValue + N'''', N'NULL')
                + N' FROM ' + @TableSchema + N'.' + @TableToUpdate + N' D WHERE CAST(D.' + @COLUMN + ' AS NVARCHAR) = ' + ISNULL(N'''' + @TestValue + N'''', N'NULL') + ';'
                + NCHAR(13) + NCHAR(10);

            SET @i = @i + 1;
        END;        
    END;    

    --PRINT SUBSTRING(@SQL, 1, 4000)
    --PRINT SUBSTRING(@SQL, 4001, 8000)
    --PRINT SUBSTRING(@SQL, 8001, 12000)
    --PRINT SUBSTRING(@SQL, 12001, 16000)
    --PRINT SUBSTRING(@SQL, 16001, 20000)
    --PRINT SUBSTRING(@SQL, 20001, 24000)
    EXEC (@SQL)
END
GO

As a usage example:

EXEC UPDATE_ALL_COLUMNS '%temp%', 'dbo', '', NULL

Parameters:

@TableNameSearchFilter - this will be used with the LIKE operator to find all the tables from your database whose names that match this value;
@TableSchema - the schema of the table (usually dbo)
@TestValue - the value to search for in ALL of the columns (and rows) of each found table;
@NewValue - the value to replace @TestValue with. Can also be NULL.

Explanation:

The EXEC statement will find ALL tables whose names contain the word 'temp', on the 'dbo' schema of your database, then search for the value '' (empty string) in ALL columns of ALL of the found tables, then replace this value with a NULL.
Obviously, if you have long(er) column/table names or the update value, make sure to update the limits on the parameters.
Make sure to first comment the last line (EXEC (@SQL)) and uncomment the lines with PRINT, just to get an idea for what the procedure does and how the final statements look like.
This is not going to work (most likely) if you want to search for the NULL value (i.e. to have @TestValue as NULL). Nevertheless, it can be easily changed to accomplish this as well, by replacing the equal sign from the WHERE clause (in the dynamic query) with IS NULL and removing the rest of the line, when @TestValue IS NULL.
Can be easily adapted to search for columns of only certain types (like VARCHAR etc).
The procedure accounts for inserting NULL values, and will only do so in NULLABLE columns.

Upvotes: 1

abc123

Reputation: 18823

The following will find and replace a string in every database (excluding system databases) on every table on the instance you are connected to:

Simply change 'Search String' to whatever you seek and 'Replace String' with whatever you want to replace it with.

--Getting all the databases and making a cursor
DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases

DECLARE @databaseName nvarchar(1000)
--opening the cursor to move over the databases in this instance
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @databaseName   

WHILE @@FETCH_STATUS = 0   
BEGIN
    PRINT @databaseName
    --Setting up temp table for the results of our search
    DECLARE @Results TABLE(TableName nvarchar(370), RealColumnName nvarchar(370), ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

    DECLARE @SearchStr nvarchar(100), @ReplaceStr nvarchar(100), @SearchStr2 nvarchar(110)
    SET @SearchStr = 'Search String'
    SET @ReplaceStr = 'Replace String'
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128)
    SET  @TableName = ''

    --Looping over all the tables in the database
    WHILE @TableName IS NOT NULL
    BEGIN
        DECLARE @SQL nvarchar(2000)
        SET @ColumnName = ''
        DECLARE @result NVARCHAR(256)
        SET @SQL = 'USE ' + @databaseName + '
            SELECT @result = MIN(QUOTENAME(TABLE_SCHEMA) + ''.'' + QUOTENAME(TABLE_NAME))
            FROM    [' + @databaseName + '].INFORMATION_SCHEMA.TABLES
            WHERE       TABLE_TYPE = ''BASE TABLE'' AND TABLE_CATALOG = ''' + @databaseName + '''
                AND QUOTENAME(TABLE_SCHEMA) + ''.'' + QUOTENAME(TABLE_NAME) > ''' + @TableName + '''
                AND OBJECTPROPERTY(
                        OBJECT_ID(
                            QUOTENAME(TABLE_SCHEMA) + ''.'' + QUOTENAME(TABLE_NAME)
                                ), ''IsMSShipped''
                                ) = 0'
        EXEC master..sp_executesql @SQL, N'@result nvarchar(256) out', @result out

        SET @TableName = @result
        PRINT @TableName

        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
        BEGIN
            DECLARE @ColumnResult NVARCHAR(256)
            SET @SQL = '
                SELECT @ColumnResult = MIN(QUOTENAME(COLUMN_NAME))
                FROM    [' + @databaseName + '].INFORMATION_SCHEMA.COLUMNS
                WHERE       TABLE_SCHEMA    = PARSENAME(''[' + @databaseName + '].' + @TableName + ''', 2)
                    AND TABLE_NAME  = PARSENAME(''[' + @databaseName + '].' + @TableName + ''', 1)
                    AND DATA_TYPE IN (''char'', ''varchar'', ''nchar'', ''nvarchar'')
                    AND TABLE_CATALOG = ''' + @databaseName + '''
                    AND QUOTENAME(COLUMN_NAME) > ''' + @ColumnName + ''''
            PRINT @SQL
            EXEC master..sp_executesql @SQL, N'@ColumnResult nvarchar(256) out', @ColumnResult out
            SET @ColumnName = @ColumnResult 

            PRINT @ColumnName

            IF @ColumnName IS NOT NULL
            BEGIN
                INSERT INTO @Results
                EXEC
                (
                    'USE ' + @databaseName + '
                    SELECT ''' + @TableName + ''',''' + @ColumnName + ''',''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                    FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END
    END

    --Declaring another temporary table
    DECLARE @time_to_update TABLE(TableName nvarchar(370), RealColumnName nvarchar(370))

    INSERT INTO @time_to_update
    SELECT TableName, RealColumnName FROM @Results GROUP BY TableName, RealColumnName

    DECLARE @MyCursor CURSOR;
    BEGIN
        DECLARE @t nvarchar(370)
        DECLARE @c nvarchar(370)
        --Looping over the search results   
        SET @MyCursor = CURSOR FOR
        SELECT TableName, RealColumnName FROM @time_to_update GROUP BY TableName, RealColumnName

        --Getting my variables from the first item
        OPEN @MyCursor 
        FETCH NEXT FROM @MyCursor 
        INTO @t, @c

        WHILE @@FETCH_STATUS = 0
        BEGIN
            -- Updating the old values with the new value
            DECLARE @sqlCommand varchar(1000)
            SET @sqlCommand = '
                USE ' + @databaseName + '
                UPDATE [' + @databaseName + '].' + @t + ' SET ' + @c + ' = REPLACE(' + @c + ', ''' + @SearchStr + ''', ''' + @ReplaceStr + ''') 
                WHERE ' + @c + ' LIKE ''' + @SearchStr2 + ''''
            PRINT @sqlCommand
            BEGIN TRY
                EXEC (@sqlCommand)
            END TRY
            BEGIN CATCH
                PRINT ERROR_MESSAGE()
            END CATCH

            --Getting next row values
            FETCH NEXT FROM @MyCursor 
            INTO @t, @c 
        END;

        CLOSE @MyCursor ;
        DEALLOCATE @MyCursor;
    END;

    DELETE FROM @time_to_update
    DELETE FROM @Results

    FETCH NEXT FROM db_cursor INTO @databaseName
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

Note: this isn't ideal, nor is it optimized

Upvotes: 1

Hiram

Reputation: 2679

this should help you:

/*
Author: sqiller
Description: Searches for a value to replace in all columns from all tables
USE: EXEC dbo.usp_Update_AllTAbles 'work', 'sqiller', 1
@search = Value to look for Replace
@newvalue = the value that will replace @search
@Test = If set to 1, it will only PRINT the UPDATE statement instead of EXEC, useful to see
        what is going to update before.
*/
CREATE PROCEDURE dbo.usp_Update_AllTAbles(
@search varchar(100),
@newvalue varchar(100),
@Test bit)
AS
BEGIN

            IF NOT EXISTS (select 1 from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Tables_to_Update')
            BEGIN
                    CREATE TABLE dbo.Tables_to_Update(
                    Table_name varchar(100),
                    Column_name varchar(100),
                    recordsToUpdate int
                    )
            END
            DECLARE @table varchar(100)
            DECLARE @column varchar(100)
            DECLARE @SQL varchar(max)

            SELECT TABLE_SCHEMA+'.'+TABLE_NAME as Table_Name, 0 as Processed INTO #tables from information_schema.tables WHERE TABLE_TYPE != 'VIEW'

            WHILE EXISTS (select * from #tables where processed = 0)
            BEGIN
                SELECT top 1 @table = table_name from #tables where processed = 0

                SELECT column_name, 0 as Processed INTO #columns from information_schema.columns where TABLE_SCHEMA+'.'+TABLE_NAME = @table


                WHILE EXISTS (SELECT * from #columns where processed = 0)
                BEGIN
                    SELECT top 1 @column = COLUMN_NAME from #columns where processed = 0

                    SET @SQL = 'INSERT INTO Tables_to_Update
                                select '''+ @table +''', '''+ @column +''', count(*) from '+@table+ ' where '+ @column +' like ''%'+ @search +'%'''
                    EXEC(@SQL)

                    IF EXISTS (SELECT * FROM Tables_to_Update WHERE Table_name = @table)
                    BEGIN
                        SET @SQL = 'UPDATE '+ @table + ' SET '+ @column + ' = REPLACE('''+@column+''','''+@search+''','''+ @newvalue +''')  WHERE '+ @column + ' like ''%'+@search+'%'''
                        --UPDATE HERE
                        IF (@Test = 1)
                        BEGIN   
                            PRINT @SQL
                        END
                        ELSE
                        BEGIN
                            EXEC(@SQL)
                        END
                    END

                    UPDATE #columns SET Processed = 1 where COLUMN_NAME = @column
                END

                DROP TABLE #columns

                UPDATE #tables SET Processed = 1 where table_name = @table
            END

            SELECT * FROM Tables_to_Update where recordsToUpdate > 0

END

Upvotes: 1

Find Replace All String Data in a SQL Server Database

Answers (3)

The following will find and replace a string in every database (excluding system databases) on every table on the instance you are connected to:

Related Questions