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

This article shows you how to achieve file operation in SQL Server, the content is concise and easy to understand, it can definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

First, judge whether the file exists or not.

The stored procedure sys.xp_fileexist is used to determine whether a file exists, and the parameter is the path of the file (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 a subdirectory

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

Exec master.sys.xp_create_subdir 'd:\ test'

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

Third, check the subdirectory structure

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

Directory: the first parameter is the directory to be queried; depth: the second parameter is the depth of the subdirectory to be displayed, and the default value is 0, indicating that all subdirectories are displayed; file: the third parameter is of bool type, which specifies whether to display files in the subdirectory (file), and the default value is 0, which means that no files are displayed, only subdirectories (directory) are displayed.

Exec master.sys.xp_dirtree 'd:\ data'

The fields returned by the stored procedure have the name of the subdirectory and the relative depth, and the parent-child relationship of the subdirectory is not shown in the result:

Fourth, delete files

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

The first parameter is the file type (File Type), the valid values are 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 file in the directory will be deleted, and the directory path must end with "\". The third parameter is the file extension (File Extension), which is commonly used as' BAK' or 'TRN'. The fourth parameter is Date, and files created earlier than that date will be deleted; the fifth parameter is the subdirectory (Subfolder), the bool type, 0 means to ignore the subdirectory, and 1 means that the files in the subdirectory will be deleted

The stored procedure cannot 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 0author D:\ test\', 'bak',@Date,0

Fifth, check the free space of the disk drive

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

Exec sys.xp_fixeddrives

Sixth, execute DOS command to operate the file

The stored procedure sys.xp_cmdshell is used to execute the DOS command, which corresponds to the xp_cmdshell advanced option of the SQL Server system, which is disabled by default. If the stored procedure is executed, an error message will be thrown:

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 risks of enabling it, it is recommended that users disable this option after code execution.

1, enable / disable the xp_cmdshell option

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

-- 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, commonly used DOS commands

This stored procedure enables users to execute DOS commands through 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 the contents of the file

Format: TYPE file_name

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

2.3 copy Fil

Format: COPY file_name new_folder

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

2.4 display Catalog

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 deleting a 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 the 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]

The above is how to implement file manipulation in SQL Server. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow 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.