There are several ways of doing this... but the easiest is perhaps to just try to read it using xp_read_file() and check if you got any result (and did not get an error). This might be a bit of an overkill if the file is large (see note below). Another drawback is that you must have permissions to read the file... so if you just want to check for existence but not necessarily have read permissions then xp_read_file cannot do this.

If xp_read_file is not a solution then you might want to consider using a directory access server / proxy table. E.g. you could create an proxy table to your entire disk and then check for the existence of the file by simply 'select'ing the (for example) size of the file from the proxy table.

In version 16 you could use the new sp_list_directory() function to check for the existence of a file within the directory.

If you don't want to do any of the above then you could choose to use xp_cmdshell to spawn a command that checks for the existence of the file and returns yes/no as a return code (or writes a file with the answer and then use xp_read_file) to get the answer - this is not very elegant so I would not recommend it.

Finally, you could write an external procedure using one of the external environment languages (C/C++, .Net, Perl, PHP, Java) and use that procedure to test for the existence of the file. If using C/C++ you could also use the external procedure 'in process' rather than using an external environement.

HTH

In SQLA v12 an enhancement was made to xp_read_file that allows the file to be read lazily. This is done by specifying a second non-zero parameter in the call to xp_read_file. When lazy reads are done the file is not actually read until it is needed... so using xp_read_file for checking for existence is not so bad! See the documentation for more info.