求SQL语句：更新数据库中所有表字段名为DomainGuid或MasterDomainGuid且值为“123”的更改为“ABC”

[javamy033]

求SQL语句：
更新数据库中所有表字段名为DomainGuid或MasterDomainGuid
且值为“123”的更改为“ABC”
----------
如有表
Pub_User
ID DomainGuid
1 123
2 456
变成：ID DomainGuid
1 ABC
2 456

Pub_Type
ID DomainGuid MasterDomainGuid
1 123 456
2 456 123
变成：
ID DomainGuid MasterDomainGuid
1 ABC 456
2 456 ABC

...
...
---------------

谢谢

[renliquan]

update Pub_User set DomainGuid=replace(DomainGuid,'123','ABC') update Pub_Type set DomainGuid=replace(DomainGuid,'123','ABC') and MasterDomainGuid=replace(MasterDomainGuid,'123','ABC')

[javamy033]

楼上这样是可以对这两个表，但我是要对整个数据库里面的100多张表进行更新 有没有直接的语句？

[yoan2014]

DECLARE @sql nVARCHAR(max)
SELECT @sql = ISNULL(@sql , '') + ' update ' + OBJECT_NAME(o.[object_id]) + ' set ' + c.name + ' = ''ABC'' where ' + c.name + ' = ''123'' 
GO
' FROM sys.COLUMNS AS c INNER JOIN sys.objects AS o ON c.[object_id] = o.[object_id] WHERE o.[type] = 'U' and c.name = 'id2'
PRINT @sql 

EXEC(@sql)

[yoan2014]

把GO去掉，加了GO会执行出错

DECLARE @sql nVARCHAR(max)
SELECT @sql = ISNULL(@sql , '') + ' update ' + OBJECT_NAME(o.[object_id]) + ' set ' + c.name + ' = ''ABC'' where ' + c.name + ' = ''123'' 
' FROM sys.COLUMNS AS c INNER JOIN sys.objects AS o ON c.[object_id] = o.[object_id] WHERE o.[type] = 'U' and c.name = 'emplno'
PRINT @sql 

EXEC(@sql)