In this article, we will explore the xp_fileexist extended stored procedure usage and other details. At the same time, we will take a look at alternative methods to this procedure.
Introduction
In some cases, database administrators may need to verify whether a particular file exists in a particular folder through SQL Server. For example, we need this function to check whether the backup file in the backup folder before starting the backup process. The biggest disadvantage of this procedure is undocumented for this reason we need to test it properly before deciding to use it in the production database environments.
Syntax
The syntax of the xp_fileexist SP is as follow:
|
1 |
EXEC xp_fileexist <filename> [ , <file_exists INT> OUTPUT] |
filename: This argument specifies the file path and file name that needs to be validated.
file_exists: This is an optional output parameter and returns information whether the file is in the specified directory. The 0 value indicates the file does not exist and the 1 value indicates the file does exist.
For example, the following query validates whether there is a tempdb database MDF file under the specified folder. The CASE statement converts the output result of the procedure into a meaningful text. When the result is 1 it returns “File Exists” text otherwise it will return “File Not Exists”.
|
1 2 3 4 5 6 7 8 9 10 |
DECLARE @File_Exists INT; EXEC Master.dbo.xp_fileexist 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\tempdb.mdf', @File_Exists OUT; SELECT CASE @File_Exists WHEN 1 THEN 'File Exists' WHEN 0 THEN 'File Not Exists' END AS Result; |
At the same time, we can print the result of the extended procedure into the message tab with help of the following query.
|
1 2 3 4 5 6 7 8 9 |
SET NOCOUNT ON; DECLARE @File_Exists INT; EXEC Master.dbo.xp_fileexist 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\tempdb.mdf', @File_Exists OUT; PRINT CASE @File_Exists WHEN 1 THEN 'File Exists' WHEN 2 THEN 'File Not Exists' END |
Advanced usage
On the other hand, the xp_fileexist extended procedure returns a dataset. When we execute the following query the File Exists and Parent Directory Exists column values will be 1.
|
1 2 |
EXEC Master.dbo.xp_fileexist 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\tempdb.mdf' |
This result shows that the file and directory are valid. When we change the tempdb file extension only the Parent Directory Exists column will show 1.
|
1 2 |
EXEC Master.dbo.xp_fileexist 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\tempdb.mdfxx' |
The result set shows that the path exists but the file is not found in this path.
Checking the existence of a particular directory
Through the xp_fileexist procedure, we can verify whether a directory exists or not exists in the file system. The main difference between the previous usage is we pass the directory path only to the filename parameter. For example, the following query checks if the error log file exists for the SQL Server instance.
|
1 2 |
EXEC Master.dbo.xp_fileexist 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\' |
As we can see the File is a Directory column that indicates the searched directory exists.
- Tip: The following query helps to find out the error log file location in SQL Server
|
1 |
EXEC xp_readerrorlog 0,1 , N'Logging SQL Server messages in file ' |
When we run the procedure for the non-exist file path only the Parent Directory Exists column returns 1 because the specified file path up-level directory exists in the file system.
|
1 2 |
EXEC Master.dbo.xp_fileexist 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Logx\' |
Alternative-1 : sys.dm_os_file_exists
With SQL Server 2017 a new DMF (Dynamic Management Function) has been introduced and this function has the same capabilities as the xp_fileexists procedure but the usage syntax is a bit different from it.
|
1 |
SELECT * FROM sys.dm_os_file_exists ( 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\ERRORLOG') |
As we can see, there is no difference in the result set between sys.dm_os_file_exists and xp_fileexists.The main advantage of the sys.dm_os_file_exists DMF is, we can easily join this function with the other tables.
Alternative-2 : xp_cmdshell
The xp_cmdshell is a very powerful extended stored procedure and is used to execute operating system commands from the SQL Server. In the default settings, the xp_cmdshell procedure is disabled to usage. For this reason, we need to enable this option to use the sp_configure. The following query will enable the xp_cmdshell option.
|
1 2 3 4 5 6 7 |
USE master; GO EXEC sp_configure 'show advanced option', '1'; RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'xp_cmdshell', 1; GO RECONFIGURE; |
Now, we check the status of the xp_cmdshell option and we have to see run_value as 1 for the proper configuration of the xp_cmdshell.
|
1 |
EXEC sp_configure 'xp_cmdshell' |
The dir is an MS-DOS command that gives information about the files and directories. So, we can use a very basic dir command to check the existence of a particular file but we can not directly execute an MS-DOS command from SQL Server. Therefore, we will execute this command with help of the xp_cmdshell procedure. After executing the query we will tackle it line by line.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
DECLARE @FilePathAndName VARCHAR(100); DECLARE @FileTable TABLE([FileName] NVARCHAR(100)); DECLARE @MsDosCommand AS VARCHAR(300); SET @FilePathAndName = '"C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\tempdb.mdf"'; SET @MsDosCommand = 'dir ' + @FilePathAndName + ' /b'; INSERT INTO @FileTable EXEC xp_cmdshell @MsDosCommand; IF EXISTS(SELECT 1 FROM @FileTable WHERE [FileName] = 'tempdb.mdf' AND [FileName] IS NOT NULL) BEGIN SELECT 'File Exists' AS Result; END; ELSE BEGIN SELECT 'File Not Exists' AS Result; END; |
|
1 2 3 |
DECLARE @FilePathAndName VARCHAR(100); DECLARE @FileTable TABLE([FileName] NVARCHAR(100)); DECLARE @MsDosCommand AS VARCHAR(300); |
In the above codes, we have used two different variables and one table variable. Now let’s explain what is their tasks.
|
1 2 |
SET @FilePathAndName = '"C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\tempdb.mdf"'; SET @MsDosCommand = 'dir ' + @FilePathAndName + ' /b'; |
In this second part of the query, we assigned the required values into the parameters. We set the MS-DOS command into the @MsDosCommand variable. The /B switch here allows only file names and extensions to return as a result of the “dir” command. If we run this command in the MS-DOS command line the result likes as below.
|
1 2 3 |
INSERT INTO @FileTable EXEC xp_cmdshell @MsDosCommand; |
In this part of the query, we executed the xp_cmdshell and populated the result into the table variable so that we will use this table to check the underlining file existence to use it.
|
1 2 3 4 5 6 7 8 9 10 11 |
IF EXISTS(SELECT 1 FROM @FileTable WHERE [FileName] = 'tempdb.mdf' AND [FileName] IS NOT NULL) BEGIN SELECT 'File Exists' AS Result; END; ELSE BEGIN SELECT 'File Not Exists' AS Result; END; |
In this last part of the code, if the file exists the code block returns File Exists result otherwise it returns File Not Exists.
Conclusion
In this article, we have learned how to verify a particular file exists in a specific folder or does not exist in SQL Server. We can use alternative methods to perform this task:
- xp_fileexist extended procedure
- sys.dm_os_file_exists dynamic management function
- xp_cmdshell extended procedure
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023