Revision: 28561
Initial Code
Initial URL
Initial Description
Initial Title
Initial Tags
Initial Language
at July 9, 2010 21:54 by Eloi
Initial Code
Create Proc SearchAndReplace
(
@SearchStr nVarChar(100),
@ReplaceStr nVarChar(100)
)
As
Begin
Set NoCount On
Declare @TableName nVarChar(256),
@ColumnName nVarChar(128),
@SearchStr2 nVarChar(110),
@SQL nVarChar(4000),
@RCTR Int
Set @TableName = ''
Set @SearchStr2 = QuoteName('%' + @SearchStr + '%','''')
Set @RCTR = 0
While @TableName Is Not Null
Begin
Set @ColumnName = ''
Set @TableName =
(
Select Min(QuoteName(TABLE_SCHEMA) + '.' + QuoteName(TABLE_NAME))
From INFORMATION_SCHEMA.Tables
Where Table_TYPE = 'BASE Table'
And QuoteName(TABLE_SCHEMA) + '.' + QuoteName(TABLE_NAME) > @TableName
And ObjectProperty (
Object_Id(QuoteName(TABLE_SCHEMA) + '.' + QuoteName(TABLE_NAME)),
'IsMSShipped'
) = 0
)
While (@TableName Is Not Null) And (@ColumnName Is Not Null)
Begin
Set @ColumnName =
(
Select Min(QuoteName(COLUMN_NAME))
From INFORMATION_SCHEMA.COLUMNS
Where TABLE_SCHEMA = PARSENAME(@TableName, 2)
And TABLE_NAME = PARSENAME(@TableName, 1)
And DATA_TYPE IN ('char', 'VarChar', 'nchar', 'nVarChar')
And QuoteName(COLUMN_NAME) > @ColumnName
)
If @ColumnName Is Not Null
Begin
Set @SQL = 'Update ' + @TableName +
' Set ' + @ColumnName + ' = ' +
'Replace(' + @ColumnName + ', ' + QuoteName(@SearchStr, '''') + ', ' + QuoteName(@ReplaceStr, '''') + ') ' +
'Where ' + @ColumnName + ' Like ' + @SearchStr2
Exec (@SQL)
Set @RCTR = @RCTR + @@ROWCOUNT
End
End
End
Select 'Replaced ' + CAST(@RCTR As VarChar) + ' occurence(s)' As 'Outcome'
End
Initial URL
Initial Description
Stored Procedure for search and replace a string in all fields of all tables of one DataBase
Initial Title
SQL Searh And Replace in ALL DataBase
Initial Tags
search, replace
Initial Language
SQL