Find and Replace a value across all tables in database
In my project I faced this problem due to typing mistakes.
End user has entered ‘goodmorning’ and ‘good morning’ interchangeably
and it was causing logic failure at application level. So task was to replace ‘goodmorning’
with ‘good morning’ in database wherever it is there. So I came up with this
simple script. It worked for me but still there is room for improvement. Please
feel free to suggest improvements, use it and share it.
Begin
Set NOCount on
SET ANSI_WARNINGS OFF
Declare @TotalRec int
Declare @Count int
Declare @TableName varchar(256)
Declare @ColumnName varchar(256)
Declare @RowNum int
Declare @SqlScript varchar(999)
Declare @SearchThis nvarchar(256)
Declare @ReplaceWith nvarchar(256)
Set @SearchThis='AAron'
Set @ReplaceWith='aaRon'
Declare
@TableColumn Table
(
RowNum int Identity(1,1),
TableName varchar(256),
ColumnName varchar(256)
)
Create Table
#TableColumn1
(
RowNum int Identity(1,1),
TableName varchar(256),
ColumnName varchar(256)
)
Insert into @TableColumn
select distinct T.TABLE_NAME --as
'TableName'
,Col.COLUMN_NAME --as 'ColumnName'
from
INFORMATION_SCHEMA.COLUMNS Col with
(nolock)
inner join INFORMATION_SCHEMA.TABLES T with (nolock) on T.TABLE_NAME=Col.TABLE_NAME and T.TABLE_TYPE='BASE TABLE'
where DATA_TYPE IN ('char', 'varchar', 'nchar', 'nVarChar','text') and T.TABLE_SCHEMA='dbo' and T.TABLE_TYPE='BASE TABLE'
Set @TotalRec=(select count(*) from @TableColumn)
Set @Count=0
while @TotalRec>@Count
Begin
Set @Count=@Count+1
Set @TableName=(Select top 1
TableName From @TableColumn T where T.RowNum=@Count)
Set @ColumnName=(Select top 1
ColumnName From @TableColumn T where T.RowNum=@Count)
Begin Try
EXEC ( 'Update '+@TableName+ ' Set '+ @ColumnName+'='+''''+@ReplaceWith+''''+ ' Where '+@ColumnName+' = '+''''+@SearchThis+'''')
End Try
Begin catch
End catch
End
Drop table #TableColumn1
SET ANSI_WARNINGS ON;
End
