SQL语句操作文件

-----先开启命令
exec sp_configure 'show advanced options', 1;
 reconfigure;
exec sp_configure 'xp_cmdshell', 1; reconfigure;

 -----1.判断文件是否存在
CREATE TABLE #tmp ([File Exists] BIT, [File is a Directory] BIT, [Parent Directory Exists] BIT)
go
INSERT INTO #tmp ([File Exists], [File is a Directory], [Parent Directory Exists])
EXEC master.dbo.xp_fileexist 'D:\Image\logo.jpg'
go
SELECT * FROM #tmp
go
DROP TABLE #tmp
go

--- File Exists                           
--- 1(文件存在)0(文件不存在)       

--- File is Deirectory
--- 1(文件夹存在)0(文件夹不存在)

--- Parent Directory Exists
---1(根目录存在)0(根目录不存在)

-----2.删除多余的图片文件
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Use GCN_B2C
go

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Alter Proc sp_FileIsExists
AS
begin
 DECLARE @Id int
 DECLARE @CardImg nvarchar(1000)
 DECLARE @RegNoImg nvarchar(1000)
 DECLARE @OrgNoImg nvarchar(1000)
 DECLARE @TaxNoImg nvarchar(1000)
 DECLARE @BankImg nvarchar(1000)
 DECLARE @flag int
 DECLARE @ImgName nvarchar(1000)
 DECLARE @ImgPath nvarchar(1000)
 Set @flag=1
   CREATE TABLE #tmpImg (
     Id int IDENTITY(1,1) NOT NULL,
  subdirectory nvarchar(2000),  --目录或者文件名
  depth int,                    --目录层次
  isfile bit default 0          --文件标志,1代表文件
  )
   --将文件夹下所有图片读取并保存在临时表
   INSERT #tmpImg exec master ..xp_dirtree 'D:\B2CJob\TestShow\WebSite\Resource\CompanyImg\',1,1
   INSERT #tmpImg exec master ..xp_dirtree 'D:\B2CJob\TestShow\WebSite\Resource\CardImg\',1,1
      --对比文件夹下和数据库中的数据是否存在，不存在的删除该图片文件
   while Exists (select Id from #tmpImg)
  begin
    select top 1 @Id=Id,@ImgName=subdirectory from #tmpImg
   
    select @flag=COUNT(Id) from jrCustomer where CardImg=@CardImg Or RegNoImg=@ImgName Or OrgNoImg=@ImgName Or TaxNoImg=@ImgName Or BankImg=@ImgName
   
    if @flag=0
    begin
     Set @ImgPath='Del D:\B2CJob\TestShow\WebSite\Resource\CompanyImg\'+@ImgName
            
    Exec master..xp_cmdshell @ImgPath,no_output
    
     Set @ImgPath='Del D:\B2CJob\TestShow\WebSite\Resource\CardImg\'+@ImgName

     Exec master..xp_cmdshell @ImgPath,no_output
    
    end
   
    delete from #tmpImg where Id=@Id
    
  end

end

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

--exec sp_FileIsExists

--exec master.dbo.xp_cmdshell 'del c:\a.txt'

--select * from jrCustomer where  DATEDIFF(DAY,Modi_Date,GETDATE())>1

--exec master.dbo.xp_delete_file 0 ,@ImgPath,'xiaopan',@DelDate

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