In this article, we are going to learn about xp_fileexist, its usage, and alternatives. To access the file system using SQL Server queries, we can use the following extended stored procedures.
- Xp_cmdshell: This command is used to execute a DOS command using a T-SQL query
- Xp_fixeddrives: This command is used to populate the list of drives and free space
- Xp_fileexist: This command is used to check whether the file exists in the specified location
Let me explain them with some simple examples.
Xp_cmdshell command
The xp_cmdshell stored procedure is an undocumented stored procedure used to execute the DOS command within the T-SQL script. This command becomes handy when you are creating a T-SQL script that requires running some DOS commands. The syntax of the xp_cmdshell command is following:
|
1 |
Exec xp_cmdshell @cmd |
In the syntax,
@Cmd: Specify the DOS command that you want to execute.
As a part of security configuration, by default, the SQL Server blocks the execution of xp_cmdshell procedure, so when you try to execute xp_cmdshell command, it returns the following error.
Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 [Batch Start Line 2]
SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned
off as part of the security configuration for this server. A system administrator can enable the use of
‘xp_cmdshell’ by using sp_configure.
For more information about enabling ‘xp_cmdshell’, search for ‘xp_cmdshell’ in SQL Server Books Online.
To rectify the error, we must enable the use of the xp_cmdshell using the sp_configure stored procedure. The xp_cmdshell is an advanced option, so first, we must enable the execution of the advanced options.
|
1 2 3 |
exec sp_configure 'show advanced options',1 reconfigure with override Go |
Once advanced options are enabled, run the following query to enable the xp_cmdshell.
|
1 2 3 |
exec sp_configure 'xp_cmdshell',1 reconfigure with override Go |
Once the configuration has been changed, the query will be executed successfully. Suppose you want to run the dir C:\Backup command using a T-SQL query. To do that, run the following query:
|
1 2 3 |
use master go exec xp_cmdshell 'C:\Backup' |
As you can see, the query has listed all the directories and files created under the C:\Backup directory.
Xp_fixeddives command
The xp_fixeddrives stored procedure is an undocumented stored procedure used to get the free space within the drive. The syntax of the xp_fixeddrives command is following:
|
1 |
Exec xp_fixeddrives |
The output will generate the drive letter and amount of free space in MB. Suppose you want to extract the list of the drives with free space, then you can run the following query.
|
1 2 3 |
use master go exec xp_fixeddrives |
As you can see, the query has listed all drives and the available space in MB.
Xp_ fileexist command
The xp_fileexist is an undocumented extended stored procedure used to check that the file exists in the specified location. The syntax of the xp_fileexist command is following:
|
1 |
Exec xp_fileexist @filename |
In the syntax:
@filename: Specify the full path of the directory in which you want to locate the file. The parameter is mandatory, and the data type is varchar.
Let us understand the command with some examples.
Example 1: When the file and directory both exist
This example shows the output of the xp_fileexist command when the file and directory exist on the specified location. We want to verify that the backup file has been created on the C:\Backup directory. For that purpose, run the following command.
|
1 2 3 |
use master go exec xp_fileexist 'C:\Backup\studentdb.bak' |
As you can see in the above image, the value of the column named File exists, and the Parent directory exists is one (1), which means that the file and directory both exist on a specified location.
Example 2: When the directory exists, but the file does not exist
This example shows the output when the file and directory do not exist in the specified location. We want to verify that the backup file has been created on the C:\Backup directory. For that purpose, run the following command:
|
1 2 3 |
use master go exec xp_fileexist 'C:\Backup\studentdb1.bak' |
As you can see in the above image, the column named File Exists is zero (0), and the value of a column named Parent Directory Exists is one (1), which means that the file does not exist. The directory exists in the specified location.
Example 3: When the directory and file do not exists.
This example shows the output of the command when the file and directory do not exist in the specified location. Suppose we want to verify that the backup file has been created on the D:\Backup directory. For that purpose, run the following command:
|
1 2 3 |
use master go exec xp_fileexist 'D:\Backups\studentdb1.bak' |
As you can see in the above image, the value of the File exists, File is a directory, and Parent directory exists is zero (0), which indicates that the file and directory are not present on the specified location.
Example 4: Print the custom message about the file’s availability
In this example, I have written a code that verifies that the file is present at the specified location. If the file is available, then the command prints File is present, or it prints File is not present.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
use master go declare @Output int exec xp_fileexist 'C:\Backup\studentdb.bak', @Output OUTPUT if @Output = 1 begin Print 'File exists on the specified location' end if @output =0 begin Print 'File exists on the specified location' End |
As I mentioned, xp_fileexist is an undocumented extended stored procedure, so while implementing it in an application, we must validate the functionalities and output generated after applying the patches, hotfixes, and SQL Server upgrades.
In SQL Server 2017, a new dynamic management function named sys.dm_os_file_exists replaced xp_fileexist stored procedure. The output returned by the sys.dm_os_file_exists and xp_fileexist is the same. For example, we want to check the backup file is present in the directory or not. To verify, run the following query:
|
1 2 3 |
declare @FileLocation varchar(max) set @FileLocation = 'C:\Backup\studentdb.bak' select * from sys.dm_os_file_exists (@FileLocation) |
As you can see, the value of file_exists and parent_directory_exists column is one (1), which indicates that the file exists on the C:\Backup location.
Summary
In this article, we learned about the various extended stored procedures that are used to access the file system using T-SQL queries. They include as follows:
- xp_cmdshell
- Xp_fileexist
- Xp_fixeddrives
He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com
- Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
- Changing the location of FILESTREAM data files in SQL Database - October 14, 2022
- Manage SQL Databases in CentOS: Manage filegroups of user databases - October 5, 2022