在master數(shù)據(jù)庫中，SQL Server提供系統(tǒng)擴(kuò)展的存儲過程，其中有一些存儲過程的命名以xp_開頭，用于處理操作系統(tǒng)的文件。
一，判斷文件是否存在
存儲過程sys.xp_fileexist 用于判斷文件是否存在，參數(shù)是文件（file）的路徑或目錄的路徑：
|
1
|
exec master.sys.xp_fileexist 'D:\test.txt' |
該存儲過程返回的結(jié)果集有一行數(shù)據(jù)，三個字段，如下圖：
二，創(chuàng)建子目錄
存儲過程 sys.xp_create_subdir 用于創(chuàng)建子目錄，參數(shù)是子目錄的路徑：
|
1
|
exec master.sys.xp_create_subdir 'D:\test' |
執(zhí)行存儲過程，系統(tǒng)返回消息：Command(s) completed successfully，說明子目錄創(chuàng)建成功。
三，查看子目錄結(jié)構(gòu)
存儲過程sys.xp_dirtree 用于顯示當(dāng)前目錄的子目錄，該存儲過程有三個參數(shù)：
- directory：第一個參數(shù)是要查詢的目錄；
- depth ：第二個參數(shù)是要顯示的子目錄的深度，默認(rèn)值是0，表示顯示所有的子目錄；
- file ：第三個參數(shù)是bool類型，指定是否顯示子目錄中的文件（file），默認(rèn)值是0，表示不顯示任何文件，只顯示子目錄（directory）；
|
1
|
exec master.sys.xp_dirtree 'D:\data' |
該存儲過程返回的字段有子目錄名稱和相對深度，返回的結(jié)果中并沒有顯示子目錄的父子關(guān)系：
四，刪除文件
存儲過程 sys.xp_delete_file 用于刪除文件，該存儲過程有5個參數(shù)：
- 第一個參數(shù)是文件類型（File Type），有效值是0和1，0是指備份文件，1是指報表文件；
- 第二個參數(shù)是目錄路徑（Folder Path）， 目錄中的文件會被刪除，目錄路徑必須以“\”結(jié)尾；
- 第三個參數(shù)是文件的擴(kuò)展名（File Extension），常用的擴(kuò)展名是'BAK' 或'TRN'；
- 第四個參數(shù)是Date，早于該日期創(chuàng)建的文件將會被刪除；
- 第五個參數(shù)是子目錄（Subfolder），bool類型，0是指忽略子目錄，1是指將會刪除子目錄中的文件；
該存儲過程并不可以刪除所有的文件，系統(tǒng)限制它只能刪除特定類型的文件。
|
1
2
|
declare @Date datetime = dateadd(day,-30,getdate())exec master.sys.xp_delete_file 0,'D:\test\','bak',@Date,0 |
五，查看磁盤驅(qū)動的空閑空間
存儲過程 sys.xp_fixeddrives用于查看磁盤驅(qū)動器剩余（free）的空間
|
1
|
exec sys.xp_fixeddrives |
六，執(zhí)行DOS命令操作文件
存儲過程sys.xp_cmdshell 用于執(zhí)行DOS命令，該功能對應(yīng)SQL Server系統(tǒng)的xp_cmdshell高級選項，默認(rèn)情況下，該選項是禁用的，執(zhí)行該存儲過程，系統(tǒng)會拋出錯誤消息：
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.
因此，在執(zhí)行該存儲過程之前，必須啟用xp_cmdshell選項，由于啟用該選項有潛在的風(fēng)險，建議用戶在執(zhí)行代碼之后，禁用該選項。
1，啟用/禁用xp_cmdshell選項
xp_cmdshell選項屬于系統(tǒng)的高級選項，執(zhí)行以下代碼，允許用戶修改高級選項：
|
1
2
3
4
5
6
|
-- 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 |
使用以下代碼啟用xp_cmdshell選項：
|
1
2
3
4
5
6
|
-- To enable the feature. exec sp_configure 'xp_cmdshell', 1; go -- To update the currently configured value for this feature. reconfigure; go |
使用以下代碼禁用xp_cmdshell選項：
|
1
2
3
4
5
6
|
-- To disable the feature. exec sp_configure 'xp_cmdshell', 0; go -- To update the currently configured value for this feature. reconfigure; go |
2，常用的DOS命令
該存儲過程使得用戶可以通過TSQL命令執(zhí)行DOS命令，
|
1
|
exec sys.xp_cmdshell 'command_string' |
2.1 建立新文件或增加文件內(nèi)容
格式：ECHO 文件內(nèi)容＞file_name
|
1
|
exec master.dbo.xp_cmdshell 'echo abc > D:\share\test.txt' |
2.2 查看文件內(nèi)容
格式：TYPE file_name
|
1
|
exec master.dbo.xp_cmdshell 'type D:\share\test.txt' |
2.3 復(fù)制文件
格式： COPY file_name new_folder
|
1
|
exec master.dbo.xp_cmdshell 'copy D:\test\test.txt D:\share\' |
2.4 顯示目錄
格式：DIR folder
|
1
|
exec master.dbo.xp_cmdshell 'dir D:\share\' |
2.5 創(chuàng)建目錄
格式：MD folder_name
|
1
|
exec master.dbo.xp_cmdshell 'md D:\share\test\' |
2.6 刪除目錄
格式：RD folder
|
1
|
exec master.dbo.xp_cmdshell 'rd D:\share\test' |
2.7 刪除文件
格式：DEL file_name
|
1
|
exec master.dbo.xp_cmdshell 'del D:\share\test.txt' |
2.8 重命名文件
格式：REN [盤符：][路徑]〈舊文件名〉〈新文件名〉
|
1
|
exec master.dbo.xp_cmdshell 'ren D:\test\test.txt new.txt' |
2.9 移動文件
格式：MOVE file_name new_folder
|
1
|
exec master.dbo.xp_cmdshell 'move D:\test\new.txt D:\share\' |
2.10 切換目錄
格式：CD[盤符：][路徑名][子目錄名]
總結(jié)
以上所述是小編給大家介紹的SQL Server 文件操作方法，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復(fù)大家的。在此也非常感謝大家對服務(wù)器之家網(wǎng)站的支持！
原文鏈接：https://www.cnblogs.com/ljhdo/archive/2018/02/23/4996060.html
