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

Other Articles of Interest

Next Article : How to read a file using SQL in a SQL Server stored procedure
Previous Article : How to count the number of distinct field values in a table in SQL Server

Tags - SQL

Comments and Questions

There's no register feature and no need to give an email address if you don't want to. All messages will be reviewed before being displayed. Comments may be merged or altered slightly, such as if it contains an email or website address.

You can decline to give a name; if that is the case, the comment will be attributed to a random star. A name is preferred, even if it's a random made-up one by yourself.

If you give an email address, you may receive an email notifying you when someone else has added a comment to the same page. In the email will be a link to unsubscribe to further notifications.