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.

Checking Files and Folders in SQL Server

 

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.

Checking Files and Folders in SQL Server

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.

Checking Files and Folders in SQL Server

The query result worked the same as xp_fileexist.

Good luck to everyone in business and life.

282 Views

Yavuz Selim Kart

I try to explain what I know in software and database. I am still improving myself by doing research on many programming languages. Apart from these, I am also interested in Graphic Design and Wordpress. I also have knowledge about SEO and Social media management. In short, I am a determined person who likes to work hard.

You may also like...

Don`t copy text!