Nisarg Upadhyay
xp_cmdshell

Understanding xp_fileexist and its usage

July 9, 2021 by

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.

  1. Xp_cmdshell: This command is used to execute a DOS command using a T-SQL query
  2. Xp_fixeddrives: This command is used to populate the list of drives and free space
  3. 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:

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.

Once advanced options are enabled, run the following query to enable the xp_cmdshell.

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:

xp_cmdshell

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:

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.

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:

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.

basic example: xp_fileexist

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:

Directory exist but file does not exist

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:

file and directory does not exist

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.

Print custom message

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:

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.

Output of dm_os_file__exists

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:

  1. xp_cmdshell
  2. Xp_fileexist
  3. Xp_fixeddrives

Nisarg Upadhyay
DBAtools, Utilities

About Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. 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

165 Views