应用场景:最近需要部署一套测试系统,如果将数据库清理掉,那么数据缺失,保护用户的的真实身份证号码,防止泄露,同时保证数据完整性,就想到 直接将系统中所有的真实身份证进行一次修改,但是数据库的表比较多,一张一张表去修改太耗费时间了,所以采用了获取库中所有表,并根据表名获取对应字段的值(由于是一套系统所以在建表的时候身份证字段名称都是一致)。
declare @tName nvarchar(20)
declare @colName nvarchar(20)
declare @str nvarchar(max)
declare mycursor cursor FOR (SELECT DISTINCT col.name AS cl_name,tb.name AS tab_name FROM sys.columns col
LEFT JOIN sys.objects AS tb ON tb.object_id = col.object_id
WHERE tb.type='U' AND col.name LIKE '%ID%' AND col.name IN('CardID'))
open mycursor
fetch next from mycursor into @colName,@tName
while @@FETCH_STATUS=0
begin
set @str='select '+@colName+' from '+@tName
--SET @str='UPDATE '+@tName+' SET '+@colName+'=REPLACE('+@colName+',SUBSTRING('+@colName+',2,2),SUBSTRING('+@colName+',2,2)+1)'
print @str
exec(@str)
fetch next from mycursor INTO @colName,@tName
end
close mycursor
deallocate mycursor
关键点:游标、SUBSTRING,REPLACE,SYS.OBJECTS,SYS.COLUMS