mssql 对文件夹里面文件进行重命名，前面加上时间戳

最新推荐文章于 2022-05-31 14:06:46 发布

弘恩

最新推荐文章于 2022-05-31 14:06:46 发布

阅读量1.4k

点赞数

本文链接：https://blog.csdn.net/c_enhui/article/details/9137987

版权

alter proc sp_filerename ( @dirpath varchar(1024))
as 
declare   @dirsql varchar(4000),@sql varchar(4000)

set @dirsql= ' dir ' + @dirpath +' /b/a'
create table #tmp ( dirfile varchar(1024),mark char(1)  default 'N' ) 
insert #tmp
exec  xp_cmdshell @dirsql 
 
 declare c cursor for 
 select 'exec xp_cmdshell '' rename ' + @dirsql 
 + dirfile 
 + convert(varchar(8),GETDATE(),112) 
 + '_'
 + right('0'+cast(DATEPART(hour,getdate())as varchar),2)
  +right('0'+cast(DATEPART(minute,getdate())as varchar),2)
  +right('0'+cast(DATEPART(second,getdate())as varchar),2) 
  +'_'+ dirfile + ''''  
   from #tmp 
   where dirfile is not null 
  open c 
  fetch next from c into @sql 
  while @@FETCH_STATUS = 0 
  begin 
  exec(@sql);
    fetch next from c into @sql 
  end 
  close c 
  deallocate c
  drop table #tmp;