How to test if a file exists in SQL Server

SQL Server is all about data, its not about files or folders but there are times when you need SQL Server to know if a file exists for example if you need a script to load a file into a database table. You shouldn't just go ahead and know the file exists, what if there's an error and how would you handle it. This tells you how to find out if the file exists so you can handle it.

SQL Server has a built in function that you can use to check if the file exists, it's called :-

master.dbo.xp_Fileexist

To use the function, you need to pass it a filename. When you pass it a filename, it will return three fields :-

File Exists
File is a Directory
Parent Directory Exists

If you need to know only if the file exists, you can pass an Output parameter to the function call and act on the value. Example below :-

    declare @value int

    exec master.dbo.xp_Fileexist 'C:\tempeadme.txt', @value Output

    Select @value

The value of @value will either be 1 or 0 if the file exists. Instead of hardcoding the file, you can pass a variable to the function call but you need to make sure it is a VARCHAR and not a VARCHAR(MAX) as the latter will return a very unhelpful error and you will then need to spend time to figuring it out. Please also note that the variable @value has to be an int not a bit even though a bit seems more appropriate otherwise it won't work.

If you wanted to turn that into a function again so that you can call it without that many lines, use the following own made function. Once the function is created, you can use again and again without having to use the above three lines.

    Create or alter Function Fexist(@fname varchar(1024)) returns int
        Begin
            declare @value int
            exec master.dbo.xp_Fileexist @fname , @value Output
            return @value
        End
    go

You can see it in use by the following SQL script.

    Create table files (Filename varchar(200))

    Insert into files values ('C:\tempeadme.txt')

    Select Filename, case when dbo.Fexist(Filename) = 1 then 'Exists' else 'Not Exist' End as Existing from files

Tags - SQL

Last Modified : June 2023