Method 1:
Exec sp_msforeachtable @ command1 = N'
Declare @ s nvarchar (4000), @ tbname sysname
Select @ s = n''', @ tbname = n ''? ''
Select @ s = @ s + n'', ''+ quotename (. name) + N' = replace (''+ quotename (. name) + n'', n'''''a''', n'''bb '''')''
From syscolumns a, policypes B
Where a. id = object_id (@ tbname)
And a. xusertype = B. xusertype
And B. name like n'' % char''
If @ rowcount> 0
Begin
Set @ s = stuff (@ s, N '''')
Exec (n'update' + @ tbname + ''set'' + @ s)
End'
Method 2:
Declare @ t varchar (255), @ c varchar (255)
Declare table_cursor cursor
Select a. name, B. name from sysobjects a, syscolumns B, policypes c
Where a. id = B. id and a. xtype = 'U' and c. name in (-- here is the type to be replaced
'Char ', 'nchar', 'nvarchar ', 'varchar', 'text', 'ntext' -- if your text (ntext) type does not exceed 8000 (4000) length.
)
Declare @ str varchar (500), @ str2 varchar (500)
-- Here is the original character you want to replace
Set @ str = 'A'
-- Here is the new character you want to replace
Set @ str2 = 'bb'
Open table_cursor fetch next from table_cursor into @ t, @ c
While (@ fetch_status = 0)
Begin
Exec ('Update ['+ @ t +'] set ['+ @ c +'] = replace (cast (['+ @ c +'] as varchar (8000 )), ''' + @ str + ''', ''' + @ str2 + ''')')
Fetch next from table_cursor into @ t, @ c
End
Close table_cursor
Deallocate table_cursor;
