Posted By

Eloi on 07/09/10


Tagged

search replace sp sqlserver StoredProcedure


Versions (?)

Who likes this?

2 people have marked this snippet as a favorite

Eloi
Tyster


SQL Searh And Replace in ALL DataBase


 / Published in: SQL
 

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

  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  

You need to login to post a comment.