sp_delete_files

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_delete_files]    Script Date: 01/09/2013 07:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
exec sp_delete_files @folder = 'B:\SQL_Performance\TraceCollection\TraceFiles\'
    ,@ext  = 'trc'
    ,@minutesgoback  = -60
*/

ALTER proc [dbo].[sp_delete_files]
(
    @folder varchar(100)
    ,@ext varchar(10)
    ,@minutesgoback int = -6000
)
as
begin

    set nocount on;
    declare @run int = 1 , @reccount int = 0
    declare @sql nvarchar(4000), @filename varchar(100), @tablename varchar(100), @param_definition nvarchar(4000), @folderlogs varchar(100)

    --set @folderlogs = @folder +'\logs\'
    set @folderlogs = @folder
   

    ------------------------------------------------

    set @sql = 'DIR ' + '"' + @folderlogs + '\*.'+@ext+'"'
    --print @sql

    if(OBJECT_ID('tempdb..#tmp_tbl')>0)
        drop table #tmp_tbl
       
    CREATE TABLE #tmp_tbl (Name varchar(400), Work int IDENTITY(1,1))

    INSERT #tmp_tbl EXECUTE master.dbo.xp_cmdshell @sql

    DELETE #tmp_tbl WHERE ISDATE(SUBSTRING(Name,1,10)) = 0 OR SUBSTRING(Name,40,1) = '.'
    --select convert(datetime,SUBSTRING(Name,1,20)),SUBSTRING(Name,40,100),dateadd(MINUTE,@minutesgoback,GETDATE()), *
    --From #tmp_tbl
    --where convert(datetime,SUBSTRING(Name,1,20)) < dateadd(MINUTE,@minutesgoback,GETDATE())

    ---------------------------------------------------


    declare tmp_cur cursor for
    SELECT SUBSTRING(Name,40,100) AS Files FROM #tmp_tbl where convert(datetime,SUBSTRING(Name,1,20)) < dateadd(MINUTE,@minutesgoback,GETDATE())

    open tmp_cur
    Fetch next from tmp_cur into @filename

    while @@fetch_status = 0
    begin
    print @filename
    set @tablename = REPLACE(@filename,'.'+@ext,'')
    print 'deleteing '+@tablename


    set @filename = ltrim(RTRIM(@filename)) 

    set @sql = 'del '+ @folder+ @filename
    if @run = 0
        print @sql 
    if @run = 1
        EXECUTE master.dbo.xp_cmdshell @sql
     
     


     
    Fetch next from tmp_cur into @filename
    print '---'
    end

    close tmp_cur
    deallocate tmp_cur

    ------------------------------------------------------
 


end