更新表中字段所有前缀"_"

--声明，此代码大部分来自网上

begin
 declare @ttablename varchar(200),@tcolumname varchar(200);
 declare @sql nvarchar(1000);
 declare @temp varchar(200);
 declare @temp1 varchar(200);
 --定义游标
 declare test_cursor cursor fast_forward for SELECT
       d.name AS tablename,     
       a.name AS coiumname      
FROM   dbo.syscolumns AS a      
       INNER JOIN dbo.sysobjects AS d
            ON a.id = d.id
            AND d.xtype = 'U'
            AND d.status >= 0    
            and d.crdate>='2014-03-08' 
            and a.name like '[_]%'
ORDER BY d.name;
--打开游标
open test_cursor;
--填充数据
fetch next from test_cursor into @ttablename,@tcolumname;
--检索到记录才处理
while @@FETCH_STATUS=0
begin
 --更新记录
 set @temp=SUBSTRING(@tcolumname,2,len(@tcolumname));
 set @temp1=@ttablename+'.'+@tcolumname;
 set @sql='sp_rename '''+@temp1+''','''+@temp+''',''column''';
 print @sql;
 EXEC sp_executesql @sql;

 --填充下一条记录
 fetch next from test_cursor into @ttablename,@tcolumname;
end;
--关闭游标
close test_cursor;
--释放游标
deallocate test_cursor;
end;

 

结果如图：