Avatar of Mike Eghtebas
Mike Eghtebas
Flag for United States of America

asked on 

Recursive file and folder audit... T-SQL

Starting with the following files in drive C:

             c.xls  <-- this file is in Folder_1

Note: There could be many levels of subfolders. I am trying to read file names and folder names to enter them in a table like:

ID_PK           Name               ID_FK            FileYN
1                C:                      null
     2          Folder_1                  1    <-- meaning it is in C:
     3          c.xls                         2                    Yes
     4          FolderA                   2
     5          a.xls                        4                    Yes
     6          b.xls                        4                    Yes
     7         Folder_2                  1
     8          d.xls                        7                    Yes
     9          e.xls                        7                    Yes

Question: Could this be done recursively to include all .xls files, folders, and subfolders (regardless it has .xls or not)?

Order of entries is not important. I will, at some point, expand this to include .doc, .png, etc.

There is an undocumented PROC which does something similar. I wonder if it could be modified to list select files and folders, .xls for example which we can pass as a parameter, in this table as shown above. This proc is discussed at: http://www.kodyaz.com/articles/file-folder-list-xp_dirtree-sql-server-stored-procedure-recursive-cte.aspx
Microsoft SQL ServerMicrosoft SQL Server 2008Microsoft SQL Server 2005

Avatar of undefined
Last Comment

8/22/2022 - Mon