--create the base tables
CREATE TABLE [dbo].[Directory_Contents_Stage] (
  [dir_output]  varchar(255)
)
GO

CREATE TABLE [dbo].[Directory_Contents_Stage2] (
  [dir_output]  varchar(255)
)
GO

CREATE TABLE [dbo].[Directory_Contents_Directories] (
   [dir_name]  varchar(255)
 , [dir_date]  datetime
)
GO

CREATE TABLE [dbo].[Directory_Contents] (
   [dirname]  varchar(255)
 , [File_Name]  varchar(255)
 , [Create_Time] datetime
 , [File_Size]  int
)
GO

--set the path
DECLARE @basepath varchar(40)
SET @basepath = 'dir C:\drivers\'

INSERT INTO Directory_Contents_Stage(dir_output) EXEC master..xp_cmdshell  @basepath
go
--first get all directory names (except where they = . or .. )
INSERT INTO Directory_Contents_Directories ([dir_name], [dir_date])
     SELECT   SUBSTRING(dir_output,37,(LEN(dir_output)-36)) AS [dir_name]
    , CONVERT(datetime,SUBSTRING(dir_output,1,17),103) AS [dir_date]
      FROM  Directory_Contents_Stage
     WHERE  (SUBSTRING(dir_output,1,1) <> ' '
	   AND (IsNumeric(SUBSTRING(dir_output, 1, 2))=1)
	   AND (IsNumeric(SUBSTRING(dir_output, 16, 1))=1)
	   AND  SUBSTRING(dir_output,37,1) <> '.'
	   AND  SUBSTRING(dir_output,38,1) <> '.'
       AND  SUBSTRING(dir_output,22,5) = '<DIR>')
go

--now use all the directories above as a new path to search

DECLARE @dirnm varchar(40)
DECLARE @dirnm2 varchar(40)
--for each @dirnm in (SELECT [dir_name] FROM Directory_Contents_Directories)
DECLARE dirname_cursor CURSOR FOR
SELECT dir_name FROM Directory_Contents_Directories

OPEN dirname_cursor

-- Perform the first fetch.
FETCH NEXT FROM dirname_cursor
INTO @dirnm

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dirnm2 = 'dir C:\drivers\' + @dirnm
INSERT INTO Directory_Contents_Stage2 (dir_output) EXEC master..xp_cmdshell  @dirnm2
   -- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM dirname_cursor
   INTO @dirnm
END

CLOSE dirname_cursor
DEALLOCATE dirname_cursor
GO

---------------  THIS PART NOT WORKING PROPERLY! -------------

--now all filenames from all directories found within @basepath have been copied
--to the table Directory_Contents_Stage2, so parse further.

--  [dirname]  varchar(255)
--  [File_Name]  varchar(255)
--  [Create_Time] datetime
--  [File_Size]  int
DECLARE @dirlongnm varchar(400)
DECLARE @dirlongnm2 varchar(4000)
DECLARE @dirlongnm1 varchar(12)
DECLARE @dirlongnm1a varchar(400)
DECLARE @dirnma varchar(12)
DECLARE @numfiles int
DECLARE @fileflag int 

SET @dirlongnm1 = 'Directory of'
SET @fileflag = 0
SET @dirlongnm1a = '?'

DECLARE dirname2_cursor CURSOR FOR
SELECT dir_output FROM Directory_Contents_Stage2
OPEN dirname2_cursor

-- Perform the first fetch.
FETCH NEXT FROM dirname2_cursor
INTO @dirlongnm2
--

--
WHILE @@FETCH_STATUS = 0
BEGIN
--get the part of the string that may read 'Directory of' as we need this to know
--where the files came from.
SET @dirnma = SUBSTRING(@dirlongnm2,2,13)
--check to see if it = 'Directory of'
	IF (@dirnma = @dirlongnm1)
      BEGIN
			-- it does, so get the directory name
			 SET @dirlongnm =  SUBSTRING(@dirlongnm2,14,(LEN(@dirlongnm2)-12))
	  END 
		--check the string we are reading out is correct format
	IF  (SUBSTRING(@dirlongnm2,1,1) <> ' '
	   AND IsNumeric(SUBSTRING(@dirlongnm2, 1, 2))=1
	   AND IsNumeric(SUBSTRING(@dirlongnm2, 16, 1))=1
       AND  SUBSTRING(@dirlongnm2,22,5) <> '<DIR>')
		 BEGIN
				--string is OK to parse and write to table

				--create some dummy values to overwrite, one for each entry found.
				INSERT Directory_Contents VALUES ('?', '??',getdate(),0)

				--write the parts of the string to the fields of the table
			UPDATE Directory_Contents 
			 SET  [File_Name] =  SUBSTRING(@dirlongnm2,37,(LEN(@dirlongnm2)-36)),
			[Create_Time] =  CONVERT(datetime,SUBSTRING(@dirlongnm2,1,17),103),
			[dirname] = @dirlongnm
			--[File_Size] = LTRIM(SUBSTRING(@dirlongnm2,19,36))

		END
   -- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM dirname2_cursor
   INTO @dirlongnm2
	
END
GO

CLOSE dirname2_cursor
DEALLOCATE dirname2_cursor
GO

--select * from Directory_Contents_Directories
--select * from Directory_Contents_Stage
select * from Directory_Contents_Stage2
select * from Directory_Contents