更数库里面包含某个字段的所有表的数据

create proc sp_UpdateTYRZH
as
create table tempTable
(
id int not null identity(1,1) primary key,
tableName varchar(200),
columnName varchar(200)
)

insert into tempTable(tableName,columnName) SELECT b.name as TableName,a.name as columnname
From syscolumns a INNER JOIN sysobjects b --插入数据到临时表
ON a.id=b.id
AND b.type='U'
AND a.name like '%TYRZH%'

declare @aa varchar(200)
declare @total int ,@tot int ,@tablename varchar(200),@columnname varchar(200)--如果知道表中的个数就可以少定义一个变量
select @total=count(*) from tempTable
set @tot=1
while @tot<=@total
begin
set @tablename =''
select @tablename=tableName,@columnname=columnname from tempTable where id=@tot
set @aa='update'+QUOTENAME(@tablename)+'set '+@columnname+'=RIGHT(''00000000''+CONVERT(VARCHAR(100),'+@columnname+'),8) where len('+@columnname+')=7' --更新该字段长度为7的数据，自动补齐8位，不足的前面加0;
exec(@aa)
set @tot=@tot+1
end
if(@tot>=@total)
begin
print('更新成功！')
drop table tempTable;
end