Accessing the Windows File System from SQL Server

Extended Stored Procedure - xp_cmdshell - Ability to execute any DOS command line code.
(Platform = SQL Server 2000 and SQL Server 2005)

Extended Stored Procedure - xp_fixeddrives - Ability to capture the free drive space in megabytes.
(Platform = SQL Server 2000 and SQL Server 2005)

Extended Stored Procedure - xp_subdirs - Ability to capture the sub directories, but not files.
(Platform = SQL Server 2000 and SQL Server 2005)

Custom Code - snippet - Ability to capture the file names in a temporary table with xp_cmdshell.
(Platform = SQL Server 2000 and SQL Server 2005)

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