SQL Searh And Replace in ALL DataBase


/ Published in: SQL
Save to your folder(s)

Stored Procedure for search and replace a string in all fields of all tables of one DataBase


Copy this code and paste it in your HTML
  1. CREATE Proc SearchAndReplace
  2. (
  3. @SearchStr nVarChar(100),
  4. @ReplaceStr nVarChar(100)
  5. )
  6. AS
  7. BEGIN
  8. SET NoCount ON
  9.  
  10. DECLARE @TableName nVarChar(256),
  11. @ColumnName nVarChar(128),
  12. @SearchStr2 nVarChar(110),
  13. @SQL nVarChar(4000),
  14. @RCTR INT
  15.  
  16. SET @TableName = ''
  17. SET @SearchStr2 = QuoteName('%' + @SearchStr + '%','''')
  18. SET @RCTR = 0
  19.  
  20. While @TableName IS NOT NULL
  21. BEGIN
  22. SET @ColumnName = ''
  23. SET @TableName =
  24. (
  25. SELECT MIN(QuoteName(TABLE_SCHEMA) + '.' + QuoteName(TABLE_NAME))
  26. FROM INFORMATION_SCHEMA.Tables
  27. WHERE Table_TYPE = 'BASE Table'
  28. AND QuoteName(TABLE_SCHEMA) + '.' + QuoteName(TABLE_NAME) > @TableName
  29. AND ObjectProperty (
  30. Object_Id(QuoteName(TABLE_SCHEMA) + '.' + QuoteName(TABLE_NAME)),
  31. 'IsMSShipped'
  32. ) = 0
  33. )
  34.  
  35. While (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
  36. BEGIN
  37. SET @ColumnName =
  38. (
  39. SELECT MIN(QuoteName(COLUMN_NAME))
  40. FROM INFORMATION_SCHEMA.COLUMNS
  41. WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
  42. AND TABLE_NAME = PARSENAME(@TableName, 1)
  43. AND DATA_TYPE IN ('char', 'VarChar', 'nchar', 'nVarChar')
  44. AND QuoteName(COLUMN_NAME) > @ColumnName
  45. )
  46.  
  47. IF @ColumnName IS NOT NULL
  48. BEGIN
  49. SET @SQL = 'Update ' + @TableName +
  50. ' Set ' + @ColumnName + ' = ' +
  51. 'Replace(' + @ColumnName + ', ' + QuoteName(@SearchStr, '''') + ', ' + QuoteName(@ReplaceStr, '''') + ') ' +
  52. 'Where ' + @ColumnName + ' Like ' + @SearchStr2
  53. EXEC (@SQL)
  54.  
  55. SET @RCTR = @RCTR + @@ROWCOUNT
  56. END
  57. END
  58. END
  59.  
  60. SELECT 'Replaced ' + CAST(@RCTR AS VARCHAR) + ' occurence(s)' AS 'Outcome'
  61.  
  62. END

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.