Accessing the Windows File System from SQL Server

EXEC master.dbo.xp_cmdshell 'dir c:\'
GO 

EXEC master.dbo.xp_fixeddrives
GO 

EXEC master.dbo.xp_subdirs 'c:\'
GO 

/*
----------------------------------------------------------------------------
-- Object Name: Script
-- Project: Misc
-- Business Process: Supports SQL Server 2000 and 2005
-- Purpose: Capture the files in a specific directory with xp_cmdshell
-- Database: N\A
-- Dependent Objects: 
-- Called By: N\A
-- Upstream Systems: N\A
-- Downstream Systems: N\A
-- 
--------------------------------------------------------------------------------------
-- Rev | CMR | Date Modified | Developer | Change Summary
--------------------------------------------------------------------------------------
--
*/ SET NOCOUNT ON

-- 1 - Variable declarations
DECLARE @CMD1 varchar(5000) 
DECLARE @CMD2 varchar(5000)
DECLARE @FilePath varchar(200)

-- 2 - Create the #OriginalFileList temporary table to support the un-cleansed file list
CREATE TABLE #OriginalFileList (
Col1 varchar(1000) NULL
)

-- 3 - Create the #ParsedFileList temporary table to support the cleansed file list
CREATE TABLE #ParsedFileList (
PFLID INT PRIMARY KEY IDENTITY (1,1) NOT NULL,
DateTimeStamp datetime NOT NULL,
FileSize varchar(50) NOT NULL,
FileName1 varchar (255) NOT NULL
)

-- 4 - Initialize the variables
SELECT @CMD1 = ''
SELECT @CMD2 = '' 
SELECT @FilePath = 'C:\Progra~1\Micros~2\MSSQL.1\MSSQL\Log\'

-- 5 - Build the string to capture the file names in the restore location
SELECT @CMD1 = 'master.dbo.xp_cmdshell ' + char(39) + 'dir ' + @FilePath + '\*.*' + char(39)

-- 6 - Build the string to populate the #OriginalFileList temporary table
SELECT @CMD2 = 'INSERT INTO #OriginalFileList(Col1)' + char(13) +
'EXEC ' + @CMD1

-- 7 - Execute the string to populate the #OriginalFileList table
EXEC (@CMD2)

-- 8 - Delete unneeded data from the #OriginalFileList
DELETE FROM #OriginalFileList
WHERE COL1 IS NULL
DELETE FROM #OriginalFileList
WHERE COL1 LIKE '%Volume%'
DELETE FROM #OriginalFileList
WHERE COL1 LIKE '%Directory%'
DELETE FROM #OriginalFileList
WHERE COL1 LIKE '%<DIR>%'
DELETE FROM #OriginalFileList
WHERE COL1 LIKE '%bytes%'

-- 9 - Populate the #ParsedFileList table with the final data
INSERT INTO #ParsedFileList (DateTimeStamp, FileSize, FileName1)
SELECT LTRIM(SUBSTRING (Col1, 1, 20)) AS 'DateTimeStamp',
LTRIM(SUBSTRING (Col1, 21, 18)) AS 'FileSize',
LTRIM(SUBSTRING (Col1, 40, 1000)) AS 'FileName1'
FROM #OriginalFileList
-- ********************************************************************************
-- INSERT code here to process the data from the #ParsedFileList table 
-- ********************************************************************************

-- 10 - Drop the temporary tables
DROP TABLE #OriginalFileList
DROP TABLE #ParsedFileList
SET NOCOUNT OFF
GO