This post was most recently updated on March 15th, 2016
The only thing that needs to change to run this script are the database where you want this to run and the values for these two parameters:
- @stringToFind
- @stringToReplace
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 |
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 = 'Smith' SET @stringToReplace = 'Jones' 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 |
