/ Published in: SQL
Stored Procedure for search and replace a string in all fields of all tables of one DataBase
Expand |
Embed | Plain Text
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
You need to login to post a comment.
