일정 기간이 지난 파일을 삭제시켜 주는 SQL 스크립트 > db

Create procedure USP_DelOldFiles @path varchar(25),@duration int
as
declare @myquery varchar(1000)
declare @query varchar(1000)
declare @name varchar(100)
set @myquery = "exec master.dbo.xp_cmdshell 'dir "+ ltrim(rtrim(@path)) + "*.* /a/od'"
print @query
 
create table #Filenames (id int identity(1,1) ,name varchar(100))
 
insert #Filenames(name)
exec (@Myquery)
delete from #Filenames where substring(name,3,1) <> '/' or name is null or
substring(name,25,1) ='<'
 
Declare mycursor cursor for
select name from #Filenames where
convert(datetime,left(name,10)) <= getdate()-@duration
open mycursor
 
fetch next from mycursor into @name
while (@@fetch_status =0)
begin
set @query = 'exec master.dbo.xp_cmdshell "del '+@path+''+ ltrim(rtrim(substring(@name,40,59)))+'"'
--print @query
exec (@query)
fetch next from mycursor into @name
end
close mycursor
deallocate mycursor
 
drop table #Filenames

Exec USP_DelOldFiles 'c:test',30 -- 현재 날짜를 기준으로which deletes files older than todaydate-30