SQL Server file Operations

Original: SQL Server file operation

In the master database, SQL Server provides system-extended stored procedures, where some stored procedures are named starting with Xp_ and are used to process the operating system's files.

One, determine whether the file exists

The stored procedure sys.xp_fileexist is used to determine whether a file exists, and the parameter is the path to the file or directory:

exec ' D:\test.txt '

The result set returned by the stored procedure has one row of data and three fields, such as:

Second, create subdirectories

The stored procedure Sys.xp_create_subdir is used to create subdirectories where the parameters are the paths to subdirectories:

exec ' D:\test '

Executes the stored procedure, the system returns a message: Command (s) completed successfully, indicating that the subdirectory was created successfully.

Third, look at the sub-directory structure

The stored procedure Sys.xp_dirtree is used to display subdirectories of the current directory, which has three parameters:

• Directory: The first parameter is a directory to query;
• Depth: The second parameter is the depth of the subdirectory to be displayed, the default value is 0, which indicates that all subdirectories are displayed;
• File: The third parameter is the bool type, specifying whether to display the files in the subdirectory (file), the default value is 0, which means that no files are displayed and only subdirectories (directory) are displayed;

exec ' D:\data '

The field returned by the stored procedure has a subdirectory name and a relative depth, and the returned results do not show the parent-child relationship for the subdirectory:

Four, delete files

The stored procedure Sys.xp_delete_file is used to delete a file, which has 5 parameters:

• The first parameter is the file type, the valid value is 0 and 1,0 refers to the backup file, 1 refers to the report file;
• The second parameter is the directory path (folder path), the files in the directory are deleted, and the directory path must end with "\";
• The third parameter is the filename extension (file Extension), and the commonly used extension is ' BAK ' or ' TRN ';
• The fourth parameter is date, and files created earlier than that date will be deleted;
• The fifth parameter is a subdirectory (subfolder), bool type, 0 refers to ignore subdirectories, 1 refers to the deletion of files in subdirectories;

The stored procedure cannot delete all the files, and the system restricts it to delete only certain types of files.

Declare @Date datetime = DateAdd (Day,-getdate()) exec 0,'D:\test\','bak',@Date ,0

Five. View disk-driven free space

Stored procedure sys.xp_fixeddrives to view the disk drive remaining (free) space

exec sys.xp_fixeddrives

Six, execute DOS command operation file

The stored procedure Sys.xp_cmdshell is used to execute DOS commands that correspond to the xp_cmdshell advanced options of the SQL Server system, which, by default, is disabled and the system throws an error message if the stored procedure is executed:

SQL Server blocked access to procedure ' Sys.xp_cmdshell ' of component ' xp_cmdshell ' because this component is turned off a s part of the security configuration for this server. A system administrator can enable the use of the ' xp_cmdshell ' by using sp_configure. For more information on enabling ' xp_cmdshell ', search for ' xp_cmdshell ' in SQL Server Books Online.

Therefore, before executing the stored procedure, the xp_cmdshell option must be enabled and, because of the potential risk of enabling the option, it is recommended that the user disable the option after executing the code.

1, enable/disable xp_cmdshell option

The xp_cmdshell option is an advanced option for the system and executes the following code to allow the user to modify advanced options:

-- To, advanced options to is changed.   exec ' Show advanced Options ' 1 ;   Go  -- To update the currently configured value for advanced options.   Reconfigure ;   Go  

Use the following code to enable the xp_cmdshell option:

-- To enable the feature.   exec ' xp_cmdshell ' 1 ;   Go  -- To update the currently configured value for this feature.   Reconfigure ;   Go

Use the following code to disable the xp_cmdshell option:

-- To disable the feature.   exec ' xp_cmdshell ' 0 ;   Go  -- To update the currently configured value for this feature.   Reconfigure ;   Go

2, Common DOS commands

This stored procedure allows the user to execute a DOS command through a TSQL command, which is a command string:

exec ' command_string '

2.1 Creating a new file or adding content to it

Format: ECHO file contents >file_name

exec ' echo ABC > D:\share\test.txt '

2.2 Viewing the contents of a file

Format: TYPE file_name

exec ' type D:\share\test.txt '

2.3 Copying files

Format: COPY file_name new_folder

exec ' copy D:\test\test.txt D:\share\ '

2.4 Displaying the table of contents

Format: DIR folder

exec ' dir D:\share\ '

2.5 Creating a Directory

Format: MD folder_name

exec ' MD D:\share\test\ '

2.6 Deleting a directory

Format: RD folder

exec ' Rd D:\share\test '

2.7 Deleting files

Format: DEL file_name

exec ' del D:\share\test.txt '

2.8 Renaming files

Format: REN [disk:] [path]〈 old file name 〉〈 new file name]

exec ' ren D:\test\test.txt new.txt '

2.9 Moving files

Format: MOVE file_name new_folder

exec ' Move D:\test\new.txt D:\share\ '

2.10 Switching directories

Format: cd[drive letter: [pathname] [subdirectory name]

3, execute the bcp command

Reference Documentation:

xp_cmdshell Server Configuration Option

xp_cmdshell (Transact-SQL)

Summary of common DOS commands

An explanation of the echo command in DOS

SQL Server file Operations