SQL Server File Operation Method, SQL Server

SQL Server File Operation Method, SQL Server

In the master database, SQL Server provides system-extended stored procedures. Some stored procedures start with xp _ and are used to process operating system files.

1. Determine whether a file exists

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

exec master.sys.xp_fileexist 'D:\test.txt'

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

2. Create Sub-Directories

The stored procedure sys. xp_create_subdir is used to create a subdirectory. The parameter is the path of the subdirectory:

exec master.sys.xp_create_subdir 'D:\test'

When the stored procedure is executed, the system returns the message Command (s) completed successfully, indicating that the subdirectory is successfully created.

3. view the subdirectory Structure

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

• Directory: the first parameter is the directory to be queried;
• Depth: The second parameter indicates the depth of the sub-directories to be displayed. The default value is 0, indicating that all sub-directories are displayed;
• File: The third parameter is of the bool type. It specifies whether to display the file in the subdirectory. The default value is 0, indicating that no file is displayed. Only the subdirectory (directory) is displayed );

exec master.sys.xp_dirtree 'D:\data'

The returned field contains the subdirectory name and relative depth. The returned result does not show the parent-child relationship of the subdirectory:

4. delete an object

The stored procedure sys. xp_delete_file is used to delete files. The stored procedure has five parameters:

• The first parameter is File Type. Valid values 0 and 1 and 0 indicate backup files, and 1 indicate report files;
• The second parameter is the directory Path (Folder Path). The files in the directory will be deleted and the directory Path must end;
• The third parameter is the File Extension. The common Extension is 'bak' or 'trn ';
• The fourth parameter is Date. files created earlier than this Date will be deleted;
• The fifth parameter is a subdirectory (Subfolder). For the bool type, 0 indicates that the subdirectory is ignored. 1 indicates that files in the subdirectory will be deleted;

This stored procedure does not allow you to delete all files. The system limits that you can only delete specific types of files.

declare @Date datetime = dateadd(day,-30,getdate())exec master.sys.xp_delete_file 0,'D:\test\','bak',@Date,0

5. view the free space of the disk drive

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

exec sys.xp_fixeddrives

6. Execute the doscommand to operate the file

Stored procedure sys. xp_cmdshell is used to execute the doscommand. This function corresponds to the xp_cmdshell advanced option of the SQL Server system. By default, this option is disabled. When this stored procedure is executed, the system will throw an error message:

SQL Server blocked access to procedure 'sys. xp_expose shell 'of component 'xp _ expose shell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp _ Your shell' by using sp_configure. for more information about enabling 'xp _ Your shell', search for 'xp _ Your shell' in SQL Server Books Online.

Therefore, you must enable the xp_cmdshell option before executing the stored procedure. It may be risky to enable this option. We recommend that you disable this option after executing the code.

1. enable/disable xp_cmdshell

The xp_cmdshell option is an advanced option of the system. Run the following code to allow users to modify the advanced options:

-- To allow advanced options to be changed. exec sp_configure 'show advanced options', 1; go -- To update the currently configured value for advanced options. reconfigure; go 

Run the following code to enable the xp_cmdshell option:

-- To enable the feature. exec sp_configure '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 sp_configure 'xp_cmdshell', 0; go -- To update the currently configured value for this feature. reconfigure; go

2. Common doscommands

This stored procedure allows you to run the DOS command using the TSQL command,

exec sys.xp_cmdshell 'command_string'

2.1 create a new file or add File Content

Format: ECHO file content> file_name

exec master.dbo.xp_cmdshell 'echo abc > D:\share\test.txt'

2.2 View File Content

Format: TYPE file_name

exec master.dbo.xp_cmdshell 'type D:\share\test.txt'

2.3 copy a file

Format: COPY file_name new_folder

exec master.dbo.xp_cmdshell 'copy D:\test\test.txt D:\share\'

2.4 display directory

Format: DIR folder

exec master.dbo.xp_cmdshell 'dir D:\share\'

2.5 create a directory

Format: MD folder_name

exec master.dbo.xp_cmdshell 'md D:\share\test\'

2.6 delete a directory

Format: RD folder

exec master.dbo.xp_cmdshell 'rd D:\share\test'

2.7 delete an object

Format: DEL file_name

exec master.dbo.xp_cmdshell 'del D:\share\test.txt'

2.8 rename a file

Format: REN [drive letter:] [path] <old file name> <New File Name> 〉

exec master.dbo.xp_cmdshell 'ren D:\test\test.txt new.txt'

2.9 move files

Format: MOVE file_name new_folder

exec master.dbo.xp_cmdshell 'move D:\test\new.txt D:\share\'

2.10 switch Directories

Format: CD [drive letter:] [path name] [subdirectory name]

Summary

The above is the SQL Server File operation method introduced by the editor. I hope it will be helpful to you. If you have any questions, please leave a message and the editor will reply to you in time. Thank you very much for your support for the help House website!