/*
This is a handy routine for getting a list of files and directories (including all subdirectories) into a table or XML list. I couldn't decide whether to Blog it or keep it a secret. I compromised by blogging here! It needs SQL Server 2005, but a similar routine would work in SQL Server 200. You'll need to allow the use of xp_cmdshell too.

There is a problem with using the Scripting.FileSystemObject for doing this, in that, in TSQL, there is no way of iterating through a COM collection other than getting each member via its index. For some cloth-headed reason lost in the mists of time, Microsoft forgot to implement this in the Files collection of the folder object. This means that you have to know the name of the file before you can find out its name. Hmm.....

The only way to do it is

    Set oFolder = oFs.GetFolder(FullPath)

    For Each oFile In oFolder.Files
      print oFile.Name
    Next

which you can't do in TSQL with the sp_OAMethod!

I had to use the XP_CmdShell method of getting this information. you may think that walking the subdirectories to get the files might be a recursive task, but it is actually almost as easy doing it by iteration.
*/

ALTER PROCEDURE [dbo].[spGetFilePaths]
   @BaseDirectory VARCHAR(255),-- the initial directory e.g. 'c:\mypath'
   @filespec VARCHAR(10) = '*.*',--the files you want
   @wanted VARCHAR(10) = 'files',--files or directories
   @subdirectories INT = 1,--do we want the subdirectories too
   @xmlFileList XML OUTPUT-- we are essentially passing back a table

/*
This procedure returns an XML list of all the files or directories to the spec you determine, using xp_CMDShell, which needs to be allowed.  A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

to use   spGetFilePaths...
e.g.
DECLARE @FileList XML
EXECUTE spGetFilePaths 'f:pentlow\work\programs\SQL\Templates\ssc', '*.*',
    @subdirectories = 1, @XMLFileList = @FileList OUTPUT
SELECT @filelist
*/
AS
   SET NOCOUNT ON
/* declare our variables ...*/
   DECLARE @DirectoryName VARCHAR(255)
   DECLARE @Directory_ID INT
   DECLARE @command VARCHAR(255)
/* ..and our local tables */
   DECLARE @directory TABLE
      (
       Directory_ID INT IDENTITY(1, 1),
       directory VARCHAR(255),
       done INT
      )
   DECLARE @line TABLE (line VARCHAR(255))
   DECLARE @files TABLE
      (
       files_ID INT IDENTITY(1, 1),
       thepath VARCHAR(255),
       [Filename] VARCHAR(100),
       [Path] VARCHAR(255)
      )
--take off any final \
SELECT @BaseDirectory=LTRIM(RTRIM(@BaseDirectory))
IF SUBSTRING(REVERSE(@BaseDirectory),1,1)='\'
   SELECT @BaseDirectory
           =SUBSTRING(@BaseDirectory,1,LEN(@BaseDirectory)-1)

/* now loop around getting the files */
   INSERT   INTO @directory
            (directory, done)
            SELECT   @baseDirectory, 0

   WHILE 1 = 1
      BEGIN
         DELETE   FROM @line
         SELECT TOP 1
                  @Directory_ID = directory_ID, @DirectoryName = directory
         FROM     @directory
         WHERE    done = 0
         IF @@rOWCOUNT = 0
            BREAK ;
--get the files first
         SELECT   @command = 'dir "' + @directoryname + '\' + @Filespec + '" /A-D /B'
         INSERT   INTO @line
                  EXECUTE xp_cmdshell @command
         IF NOT EXISTS ( SELECT  line
                         FROM    @line
                         WHERE   line LIKE 'File Not Found' )
            INSERT   INTO @files
                     (thePath, [filename], [path])
                     SELECT   @directoryname + '\' + line, line, @DirectoryName
                     FROM     @line
                     WHERE    line IS NOT NULL
--get the directories
         DELETE   FROM @line
         SELECT   @command = 'dir "' + @directoryname + '" /AD  /B'
         INSERT   INTO @line
                  EXECUTE xp_cmdshell @command
         INSERT   INTO @directory
                  (directory, done)
                  SELECT   @directoryname + '\' + line, 0
                  FROM     @line
                  WHERE    line IS NOT NULL
         UPDATE   @directory
         SET      done = -1
         WHERE    directory_ID = @directory_ID
         IF @subdirectories = 0
            BREAK
      END
   IF @wanted = 'files'
      SET @xmlFileList = (SELECT thePath, [filename], [path]
                          FROM   @files
                         FOR
                          XML PATH('thefile'),
                              ROOT('thefiles'),
                              TYPE
                         )
   ELSE
      SET @xmlFileList = (SELECT thepath = directory FROM @directory
                         FOR
                          XML PATH('thefile'),
                              ROOT('thefiles'),
                              TYPE
                         )