SQL - Win File System

• Tara-1044200

  SSCoach

  Points: 15951

  More actions

  May 4, 2009 at 7:38 pm

  #200085

  How would i access list of files with modified date in windows directory from SQL Query.

• Ken Simmons

  SSCertifiable

  Points: 7822

  More actions

  May 4, 2009 at 8:10 pm

  #988510

  Here is a script that I use. You need to enable xp_cmdshell. I think I had a better script, but I cant seem to find it now.

  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 varchar(20) NOT NULL,

  FileSize varchar(50) NOT NULL,

  FileName1 varchar (255) NOT NULL

  )

  -- 4 - Initialize the variables

  SELECT @CMD1 = ''

  SELECT @CMD2 = ''

  SELECT @FilePath = 'C:'

  -- 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 '%%'

  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

  -- ********************************************************************************

  Select * from #ParsedFileList

  -- 10 - Drop the temporary tables

  DROP TABLE #OriginalFileList

  DROP TABLE #ParsedFileList--