How Can I Verify File Existence in SQL Server for Each Row in a Table?

Verifying File Existence in SQL Server

Problem Statement

Given a table with file paths stored in a column, the task is to determine whether each file physically exists in the file system. The goal is to add a temporary column to indicate "Yes" for existing files and "No" for non-existent files.

Solution

To achieve this, we can employ the master.dbo.xp_fileexist extended stored procedure, which checks for the existence of a specified file. However, to apply this technique to a table, a custom function is required.

Custom Function

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

Adding a Computed Column

Edit the table to add a computed column called IsExists with the expression:

dbo.fn_FileExists(filepath)

Usage

Now, you can simply select the records with existing files:

SELECT * FROM dbo.MyTable where IsExists = 1

Alternative Usage (Outside Computed Column)

To use the function outside a computed column:

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

Troubleshooting

If the function returns 0 for known files, check the folder permissions and ensure the SQL Server account has read-only access. Additionally, add the "Network Service" account to the folder security settings to grant access.

The above is the detailed content of How Can I Verify File Existence in SQL Server for Each Row in a Table?. For more information, please follow other related articles on the PHP Chinese website!