Shulou(Shulou.com)05/31 Report--

This article mainly shows you "SQL Server file operation example analysis", the content is simple and easy to understand, organized clearly, I hope to help you solve doubts, let Xiaobian lead you to study and learn "SQL Server file operation example analysis" this article bar.

In the master database, SQL Server provides system-extended stored procedures, some of which start with xp_and are used to process files from the operating system.

Determine if the 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 the path of the 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, as shown in the following figure:

Second, create subdirectories

The stored procedure sys.xp_create_subdir is used to create a subdirectory, and the parameters are the path of the subdirectory:

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

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

Third, view the subdirectory structure

The stored procedure sys.xp_dirtree displays subdirectories of the current directory and has three parameters:

directory: The first parameter is the directory to query;

depth: The second parameter is the depth of the subdirectories to be displayed. The default value is 0, indicating that all subdirectories are displayed;

file: The third parameter is bool type, specifying whether to display files in the subdirectory. The default value is 0, which means that no files are displayed, only directories are displayed.

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

The stored procedure returns fields with subdirectory names and relative depths, and returns results that do not show the parent-child relationships of subdirectories:

4. Delete files

The stored procedure sys.xp_delete_file deletes files and has five parameters:

The first parameter is File Type, valid values are 0 and 1, 0 refers to backup file, 1 refers to report file;

The second parameter is the Folder Path, where files will be deleted and the directory path must end with "\";

The third parameter is the File Extension, commonly known as 'BAK' or 'TRN';

The fourth parameter is Date, files created before that date will be deleted;

The fifth parameter is Subfolder, bool type, 0 means ignore the subdirectory, 1 means the files in the subdirectory will be deleted;

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

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

Fifth, check the free space of the disk drive

The stored procedure sys.xp_fixeddrives is used to view free space on disk drives

exec sys.xp_fixeddrives

VI. Execute DOS command operation file

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

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.

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

1, enable/disable xp_cmdshell option

The xp_cmdshell option is an advanced option for the system and allows the user to modify the advanced option by executing the following code:

-- 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

Use 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

Disable the xp_cmdshell option using the following code:

-- To disable the feature. exec sp_configure 'xp_cmdshell', 0; go -- To update the currently configured value for this feature. reconfigure; go

2. Common DOS commands

This stored procedure enables users to execute DOS commands via TSQL commands

exec sys.xp_cmdshell 'command_string'

2.1 Create new documents or add document content

Format: ECHO file content>file_name

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

2.2 view the file contents

Type file_name

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

2.3 copy files

COPY file_name new_folder

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

2.4 displaying 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 directory

Format: RD folder

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

2.7 delete files

Format: DEL file_name

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

2.8 rename files

Format: REN [letter: ][path]

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

2.9 move files

MOVE file_name new_folder

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

2.10 switch directory

Format: CD[drive: ][pathname][subdirectory name]

The above is "SQL Server file operation sample analysis" all the content of this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.