Use T-SQL to list all files in a directory

demo · 发表于 2012-5-18 13:29:42

本帖最后由 demo 于 2012-5-19 05:40 编辑

Execute the following T-SQL example scripts in Microsoft SQL Server Management Studio Query Editor to list all files and all directories in a directory using xp_cmdshell extended system stored procedure.
Warning article:
What are the security risks using cmdexec?

-- List all files in a directory - T-SQL parse string for date and filename
-- Microsoft SQL Server command shell statement - xp_cmdshell
DECLARE @PathName VARCHAR(256) ,
@CMD VARCHAR(512)
CREATE TABLE #CommandShell ( Line VARCHAR(512))
SET @PathName = 'F:\data\download\microsoft\'
SET @CMD = 'DIR ' + @PathName + ' /TC'
PRINT @CMD -- test & debug
-- DIR F:\data\download\microsoft /TC
-- MSSQL insert exec - insert table from stored procedure execution
INSERT INTO #CommandShell
EXEC MASTER..xp_cmdshell @CMD
-- Delete lines not containing filename
DELETE
FROM #CommandShell
WHERE Line NOT LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9] %'
OR Line LIKE '%<DIR>%'
OR Line is null
-- SQL reverse string function - charindex string function
SELECT
FileName = REVERSE( LEFT(REVERSE(Line),CHARINDEX(' ',REVERSE(line))-1 ) ),
CreateDate = LEFT(Line,10)
FROM #CommandShell
ORDER BY FileName
/* Results
FileName CreateDate
AdventureWorks2008.pdf 09/07/2018
AdventureWorks2008_Conceptual.pdf 09/07/2018
AdventureWorksDW.pdf 09/07/2018
AdventureWorksDW2008.pdf 09/07/2018
microsoft.lnk 03/03/2019
*/
DROP TABLE #CommandShell
GO
------------
-- Get file list in specified directory (folder)
-- T-SQL command shell - insert exec
CREATE TABLE #FileList (
Line VARCHAR(512))
DECLARE @Path varchar(256) = 'dir f:\data\'
DECLARE @Command varchar(1024) = @Path+' /A-D /B'
PRINT @Command
INSERT #FileList
EXEC MASTER.dbo.xp_cmdshell @Command
DELETE #FileList WHERE Line IS NULL
SELECT * FROM #FileList
GO
DROP TABLE #FileList
GO
-- Get directory (subdirectory, folder) list in specified directory (folder)
CREATE TABLE #DirectoryList (
Line VARCHAR(512))
DECLARE @Path varchar(256) = 'dir f:\data\'
DECLARE @Command varchar(1024) = @Path+' /A-A /B'
PRINT @Command
INSERT #DirectoryList
EXEC MASTER.dbo.xp_cmdshell @Command
DELETE #DirectoryList WHERE Line IS NULL
SELECT * FROM #DirectoryList
GO
DROP TABLE #DirectoryList
GO
------------

复制代码

账号		自动登录	找回密码
密码			注册