SQL: How do you find multiple records from an entire database, and then replace those records with 1 value

57 Views Asked by At

I hoping someone can help. Fairly new to SQL and in need of some advice.

Scenario: I am trying to find multiple records (around 50 different records) throughout all tables in my database and then replace those with 1 value. The records that I will be looking for will be exact.

The solution I'm looking for ideally needs to be scalable as the number of records can increase.

Would anyone be able to help with this.

P.S. I have been using the below script but this only sets 1 variable at a time.

    SET NOCOUNT ON 

DECLARE @stringToFind VARCHAR(100) 
DECLARE @stringToReplace VARCHAR(100) 
DECLARE @schema sysname 
DECLARE @table sysname 
DECLARE @count INT 
DECLARE @sqlCommand VARCHAR(8000) 
DECLARE @where VARCHAR(8000) 
DECLARE @columnName sysname 
DECLARE @object_id INT 

SET @stringToFind = 'VALUE TO FIND'
SET @stringToReplace = 'VALUE TO REPLACE' 

DECLARE TAB_CURSOR CURSOR  FOR 
SELECT   B.NAME      AS SCHEMANAME, 
         A.NAME      AS TABLENAME, 
         A.OBJECT_ID 
FROM     sys.objects A 
         INNER JOIN sys.schemas B 
           ON A.SCHEMA_ID = B.SCHEMA_ID 
WHERE    TYPE = 'U' 
ORDER BY 1 

OPEN TAB_CURSOR 

FETCH NEXT FROM TAB_CURSOR 
INTO @schema, 
     @table, 
     @object_id 

WHILE @@FETCH_STATUS = 0 
  BEGIN 
    DECLARE COL_CURSOR CURSOR FOR 
    SELECT A.NAME 
    FROM   sys.columns A 
           INNER JOIN sys.types B 
             ON A.SYSTEM_TYPE_ID = B.SYSTEM_TYPE_ID 
    WHERE  OBJECT_ID = @object_id 
           AND IS_COMPUTED = 0 
           AND B.NAME IN ('char','nchar','nvarchar','varchar','text','ntext') 

    OPEN COL_CURSOR 

    FETCH NEXT FROM COL_CURSOR 
    INTO @columnName 

    WHILE @@FETCH_STATUS = 0 
      BEGIN 
        SET @sqlCommand = 'UPDATE ' + @schema + '.' + @table + ' SET [' + @columnName 
                           + '] = REPLACE(convert(nvarchar(max),[' + @columnName + ']),''' 
                           + @stringToFind + ''',''' + @stringToReplace + ''')' 

        SET @where = ' WHERE [' + @columnName + '] LIKE ''%' + @stringToFind + '%''' 

        EXEC( @sqlCommand + @where) 

        SET @count = @@ROWCOUNT 

        IF @count > 0 
          BEGIN 
            PRINT @sqlCommand + @where 
            PRINT 'Updated: ' + CONVERT(VARCHAR(10),@count) 
            PRINT '----------------------------------------------------' 
          END 

        FETCH NEXT FROM COL_CURSOR 
        INTO @columnName 
      END 

    CLOSE COL_CURSOR 
    DEALLOCATE COL_CURSOR 

    FETCH NEXT FROM TAB_CURSOR 
    INTO @schema, 
         @table, 
         @object_id 
  END 

CLOSE TAB_CURSOR 
DEALLOCATE TAB_CURSOR
0

There are 0 best solutions below