Checking Files and Folders in SQL Server

Hello to everyone,

In this article, I will try to give information about how to control files and folders in SQL Server.

In some cases in SQL Server, you may want to have your file or folder checked for existence and take action accordingly.

Before SQL Server 2017, we were doing this using the xp_fileexist command.

EXEC xp_fileexist 'C:/test.txt'

I created a file called test.txt under the C drive. When you run the SQL query, you will see a result similar to the one below.

The File Exists field is returned as 1 because there is a file named test.txt in the C directory.

EXEC xp_fileexist 'C:/Program Files'

When you run the above query, you will see the following result.

Since we checked the folder, the File is a Directory field returned 1, and the Parent Directory Exists field informs us whether the folder is in the parent directory.

You will check the values in File Exists for file queries and File is a Directory for folder queries.

After SQL Server 2017, we do this using the sys.dm_os_file_exists command.

SELECT * FROM sys.dm_os_file_exists('C:/test.txt');

When you run the above query, you will see the following result.

The query result worked the same as xp_fileexist.

Good luck to everyone in business and life.