Find and Replace Column value from Over all Database

by

I saw this somewhere I tried it and it worked. You can give it a go 

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 = 'Apple' ---- what you want to replace
SET @stringToReplace="mango" ---- what you want to replace it with

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

More Related Contents:

  1. When should I use CROSS APPLY over INNER JOIN?
  2. SQL Server – Return value after INSERT
  3. SQL Server Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, =
  4. Parameterize an SQL IN clause
  5. Using the result of an expression (e.g. Function call) in a stored procedure parameter list?
  6. Emulate MySQL LIMIT clause in Microsoft SQL Server 2000
  7. When no ‘Order by’ is specified, what order does a query choose for your record set?
  8. How do I create a step in my SQL Server Agent Job which will run my SSIS package?
  9. Recursive query in SQL Server
  10. SQL Call Stored Procedure for each Row without using a cursor
  11. How to select the last record of a table in SQL?
  12. How do I create a foreign key in SQL Server?
  13. How to create table using select query in SQL Server?
  14. How do I group on continuous ranges
  15. join comma delimited data column
  16. conversion of a varchar data type to a datetime data type resulted in an out-of-range value
  17. Is it possible to perform multiple updates with a single UPDATE SQL statement?
  18. How do I pass a list as a parameter in a stored procedure?
  19. Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
  20. Get previous and next row from rows selected with (WHERE) conditions
  21. Can you have if-then-else logic in SQL? [duplicate]
  22. How to list files inside a folder with SQL Server
  23. Can SQL Server Express LocalDB be connected to remotely?
  24. SQL – Call Stored Procedure for each record
  25. WITH CHECK ADD CONSTRAINT followed by CHECK CONSTRAINT vs. ADD CONSTRAINT
  26. Add a row number to result set of a SQL query
  27. Sql Server : How to use an aggregate function like MAX in a WHERE clause
  28. How to get the next number in a sequence
  29. How to Select Every Row Where Column Value is NOT Distinct
  30. How to insert into a table with just one IDENTITY column?

Leave a Comment