Wednesday, October 31, 2012
TSQL CHANGE SPECIFIC CHARACTERS ENTIRE OF DATABASE
SELECT REPLACE(REPLACE('variable','ي','ی'),'ك','ک');
--- OR
Set Nocount on
Declare @t Table (Id int identity(1,1) primary key,
ColumnName varchar(100),TableName varchar(100),SchemaName varchar(100))
Declare @Id int,
@ColumnName varchar(100),
@TableName varchar(100),
@SchemaName varchar(100),
@Sql nvarchar(max)
Insert Into @t(ColumnName,TableName,SchemaName)
Select sys.all_columns.name, sys.objects.name,sys.schemas.name
from sys.all_columns
Inner Join sys.types on sys.types.user_type_id= sys.all_columns.user_type_id
Inner Join sys.objects on sys.objects.object_id=sys.all_columns.object_id
Inner Join sys.schemas on sys.schemas.schema_id=sys.objects.schema_id
where sys.types.system_type_id in (231,239)
and sys.objects.type='U'
While Exists(Select * from @t)
Begin
Select Top 1 @Id=Id,@ColumnName=ColumnName,@TableName=TableName,@SchemaName=SchemaName
From @t
Print '['+@SchemaName+N'].['+@TableName+N'].['+@ColumnName+N']'
Set @sql=N'
BEGIN TRY
Update ['+@SchemaName+N'].['+@TableName+N']
Set ['+@ColumnName+N']=Replace(['+@ColumnName+N'],N''ی'',N''ي'')
Where ['+@ColumnName+N'] Like N''%ی%''
Update ['+@SchemaName+N'].['+@TableName+N']
Set ['+@ColumnName+N']=Replace(['+@ColumnName+N'],N''ک'',N''ك'')
Where ['+@ColumnName+N'] Like N''%ک%''
END TRY
BEGIN CATCH
Print ''Error on update ['+@SchemaName+N'].['+@TableName+N'].['+@ColumnName+N']''
END CATCH
'
--print @sql
Exec (@Sql)
Delete @t
Where Id=@Id
End
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment