|
mssql对文件夹里面文件进行重命名，前面加上时间戳
[sql]
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;
|
