在SQL Server中检查文件是否存在？

Question

解决方案: http://www.tech-recipes.com/rx/30527/sql-server-how-to-check-if-a-file-exists-in-a-directory/

我发了一篇关于这个问题的帖子，在使用stackoverflow的问题帮助他人。

id  filepath

1   C:\vishwanath\21776656.docx
2   C:\vishwanath\vish\s_srv_req_2009.txt
3   C:\Users\dalvi\DW\DW20SharedAmd64.exe
4   C:\Users\dalvi\1.txt

我在我的数据库服务器上创建了这样的表，我在它的filepath列中存储了文件路径，现在我需要使用SQL检查文件是否存在于我的计算机上，如果存在，我需要在我的表中添加临时列，如果存在则显示“是”，否则显示“否”。

我编写了这段代码，它适用于1个文件，但我不知道如何将其应用于我的表格。

DECLARE @isExists INT
exec master.dbo.xp_fileexist 'C:\vishwanath\21776656.docx', 
@isExists OUTPUT
SELECT case @isExists 
when 1 then 'Yes' 
else 'No' 
end as isExists

最终输出应该像这样。

id  filepath                                 Isexists

1   C:\vishwanath\21776656.docx               Yes
2   C:\vishwanath\vish\s_srv_req_2009.txt     Yes
3   C:\Users\dalvi\DW\DW20SharedAmd64.exe     Yes
4   C:\Users\dalvi\1.txt                      No

- Vishwanath Dalvi

---

听起来你正在尝试使用远程SQL Server来检查本地计算机上是否存在文件。服务器很可能无法访问本地计算机的文件系统（有充分的理由）。 - paul

Answer 1

创建一个如下所示的函数：

CREATE FUNCTION dbo.fn_FileExists(@path varchar(512))
RETURNS BIT
AS
BEGIN
     DECLARE @result INT
     EXEC master.dbo.xp_fileexist @path, @result OUTPUT
     RETURN cast(@result as bit)
END;
GO

编辑你的表并添加一个计算列（IsExists BIT）。将表达式设置为：

dbo.fn_FileExists(filepath)

然后只需选择：

SELECT * FROM dbo.MyTable where IsExists = 1

更新:

如需在计算列之外使用该函数:

select id, filename, dbo.fn_FileExists(filename) as IsExists
from dbo.MyTable

更新：

如果该函数对于已知的文件返回0，则很可能存在权限问题。请确保SQL Server账户具有访问文件夹和文件的足够权限，只读权限应该足够。

是的，默认情况下，“NETWORK SERVICE”账户将无法访问大多数文件夹。右键单击相关文件夹，选择“属性”，然后点击“安全”选项卡。点击“编辑”并添加“Network Service”。点击“应用”并重新测试。

- Chris Gessler

---

@MartinSmith - 我已更新我的答案，不久将进行测试。 - Chris Gessler

我有一个函数运行良好，但当我更改SQL用户时，它始终返回所有记录的0。正如你所说，这可能是权限问题 - Windows用户可以访问文件夹和文件（添加了对网络服务的权限），但我不知道如何更改SQL用户的权限。需要帮助吗？ - PJLG

@PJLG - 我不太确定如何管理这些权限，也不知道如何确定“Network Service”用户是否可以执行该函数。听起来像是在stackoverflow上提问的好问题。 - Chris Gessler

谢谢@Chris。我想我已经找到了解决方案 - 至少它解决了问题，尽管我没有意识到所有的影响。我不得不在相关用户上激活“Sysadmin”服务器角色（在数据库外的安全性中）。 - PJLG

@Zeinab - 请查看此链接 https://dev59.com/nmYr5IYBdhLWcg3wVYsg - Chris Gessler

Answer 2

虽然没有经过测试，但你可以尝试类似以下的代码：

Declare @count as int
Set @count=1
Declare @inputFile varchar(max)
Declare @Sample Table
(id int,filepath varchar(max) ,Isexists char(3))

while @count<(select max(id) from yourTable)
BEGIN
Set @inputFile =(Select filepath from yourTable where id=@count)
DECLARE @isExists INT
exec master.dbo.xp_fileexist @inputFile , 
@isExists OUTPUT
insert into @Sample
Select @count,@inputFile ,case @isExists 
when 1 then 'Yes' 
else 'No' 
end as isExists
set @count=@count+1
END

- praveen

Answer 3

你可以使用游标来实现，但性能比while循环慢得多。 以下是代码:

set nocount on
declare cur cursor local fast_forward for
    (select filepath from Directory)
open cur;
declare @fullpath varchar(250);
declare @isExists int;

fetch from cur into @fullpath
while @@FETCH_STATUS = 0
    begin
        exec xp_fileexist @fullpath, @isExists out
        if @isExists = 1            
            print @fullpath + char(9) + char(9) + 'file exists'
        else            
            print @fullpath + char(9) + char(9) + 'file does not exists'
        fetch from cur into @fullpath
    end
close cur
deallocate cur

或者，如果您想将其集成到前端中，可以将其放入临时表中。

create proc GetFileStatus as
begin
    set nocount on
    create table #tempFileStatus(FilePath varchar(300),FileStatus varchar(30))
    declare cur cursor local fast_forward for
        (select filepath from Directory)
    open cur;
    declare @fullpath varchar(250);
    declare @isExists int;

    fetch from cur into @fullpath
    while @@FETCH_STATUS = 0
        begin
            exec xp_fileexist @fullpath, @isExists out
            if @isExists = 1                
                insert into #tempFileStatus values(@fullpath,'File exist')
            else
                insert into #tempFileStatus values(@fullpath,'File does not exists')
            fetch from cur into @fullpath
        end
    close cur
    deallocate cur
    select * from #tempFileStatus
    drop table #tempFileStatus
end

然后使用以下方式调用：

exec GetFileStatus

- devkiat

---

只是补充一下，如果您想验证远程服务器上是否存在文件，则需要共享您的文件夹。 - devkiat

Answer 4

尝试以下代码以验证文件是否存在。您可以创建一个用户函数并在存储过程中使用它。根据需要进行修改：

Set NOCOUNT ON

 DECLARE @Filename NVARCHAR(50)
 DECLARE @fileFullPath NVARCHAR(100)

 SELECT @Filename = N'LogiSetup.log'
 SELECT @fileFullPath = N'C:\LogiSetup.log'

create table #dir

(output varchar(2000))

 DECLARE @cmd NVARCHAR(100)
SELECT @cmd = 'dir ' + @fileFullPath     

insert into #dir    

exec master.dbo.xp_cmdshell @cmd

--Select * from #dir

-- This is risky, as the fle path itself might contain the filename
if exists (Select * from #dir where output like '%'+ @Filename +'%')

       begin    
              Print 'File found'    
              --Add code you want to run if file exists    
       end    
else    
       begin    
              Print 'No File Found'    
              --Add code you want to run if file does not exists    
       end

drop table #dir

- Shivkant