locked
Getting list of files, Modified Date from Windows folders/Subfolders using T-SQL RRS feed

  • Question

  • Hello

    Is there any way to get list of files, Modified Date from Windows folders/Subfolders using T-SQL?

    I tried few different SQL, but not able to get all the files within sub folders.

    Thanks in Advance

    Thursday, January 21, 2016 7:31 PM

Answers

  • XP_CMDSHELL brings with it inherent security issues, which is why it's turned off by default.

    If you want to use it to do this, consider turning it on long enough to do what you need, and then turn it off again:

    EXEC sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE;
    GO
    EXEC sp_configure 'xp_cmdshell', 1;
    GO
    RECONFIGURE;
    GO
    
    DECLARE @output TABLE (output NVARCHAR(MAX))
    DECLARE @cmd VARCHAR(8000) = 'dir c:\windows\'
    
    INSERT INTO @output
    EXEC XP_CMDSHELL @cmd
    
    SELECT   CAST(LEFT(output,20) AS DATETIME) AS modifiedDateTime,
              CASE WHEN SUBSTRING(output,25,5) = '<DIR>' THEN 'Directory'
    		      ELSE 'File' 
    		  END AS type,
    		  CAST(CASE WHEN SUBSTRING(output,25,5) = '<DIR>' THEN NULL
    		       ELSE REPLACE(SUBSTRING(output,25,15),',','')
    		  END AS INT) AS fileSize,
    		  LTRIM(CASE WHEN SUBSTRING(output,25,5) = '<DIR>' THEN SUBSTRING(output,30,999)
    		       ELSE SUBSTRING(output,40,999)
    		  END) AS name
      FROM @output
     WHERE output IS NOT NULL
       AND LEFT(output,1) <> ' '
       AND output NOT LIKE '%<DIR>%.'
     ORDER BY type, name
    
    
    EXEC sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE;
    GO
    EXEC sp_configure 'xp_cmdshell', 0;
    GO
    RECONFIGURE;
    GO

     

    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Thursday, January 21, 2016 8:25 PM
  • Hi Swetha,

    As xp_cmdshell is not encouraged for its security issue, besides the CLR procedure, you can output all the files and the modified date and time into a flat file by running the below script in a command window.

    forfiles /p "thefolderpath" /c "cmd /c echo @path @file @fdate @ftime >>d:\destination.txt" /m *.* /s


    Then you can bulk import the destination.txt using T-SQL.

    If you have any question, feel free to let me know.

    Eric Zhang
    TechNet Community Support

    • Proposed as answer by Naomi N Friday, January 22, 2016 4:31 PM
    • Marked as answer by Swetha Monpara Monday, January 25, 2016 3:29 PM
    Friday, January 22, 2016 2:35 AM

All replies

  • I think CLR procedure may be the best solution here (should be better than xp_cmdshell solution).

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, January 21, 2016 7:40 PM
  • You can use XP_CMDSHELL and, for example, use DIR command to list a content of a folder and Insert it on a table.
    Thursday, January 21, 2016 7:45 PM
  • XP_CMDSHELL brings with it inherent security issues, which is why it's turned off by default.

    If you want to use it to do this, consider turning it on long enough to do what you need, and then turn it off again:

    EXEC sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE;
    GO
    EXEC sp_configure 'xp_cmdshell', 1;
    GO
    RECONFIGURE;
    GO
    
    DECLARE @output TABLE (output NVARCHAR(MAX))
    DECLARE @cmd VARCHAR(8000) = 'dir c:\windows\'
    
    INSERT INTO @output
    EXEC XP_CMDSHELL @cmd
    
    SELECT   CAST(LEFT(output,20) AS DATETIME) AS modifiedDateTime,
              CASE WHEN SUBSTRING(output,25,5) = '<DIR>' THEN 'Directory'
    		      ELSE 'File' 
    		  END AS type,
    		  CAST(CASE WHEN SUBSTRING(output,25,5) = '<DIR>' THEN NULL
    		       ELSE REPLACE(SUBSTRING(output,25,15),',','')
    		  END AS INT) AS fileSize,
    		  LTRIM(CASE WHEN SUBSTRING(output,25,5) = '<DIR>' THEN SUBSTRING(output,30,999)
    		       ELSE SUBSTRING(output,40,999)
    		  END) AS name
      FROM @output
     WHERE output IS NOT NULL
       AND LEFT(output,1) <> ' '
       AND output NOT LIKE '%<DIR>%.'
     ORDER BY type, name
    
    
    EXEC sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE;
    GO
    EXEC sp_configure 'xp_cmdshell', 0;
    GO
    RECONFIGURE;
    GO

     

    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Thursday, January 21, 2016 8:25 PM
  • Hi Patrick

    Thanks for the response. I have tried this before, but the issue is, it only looks at specified folder, but doesn't give the list of the files from Sub folder.

    Again, Thanks for your time.

    Thursday, January 21, 2016 8:29 PM
  • Hi Naomi

    I don't have knowledge of CLR. I tried to look online if someone has done this using CLR, but didn't have luck.

    Thanks for your response.

    Thursday, January 21, 2016 8:30 PM
  • Hi Marcos

    I tried using XP_CMDSHELL, but it only gives list of the files in specified directory, but not from the sub directory.

    Thanks for your response.

    Thursday, January 21, 2016 8:32 PM
  • Yep, if you want it to explore a path fully, you're going to be involving cursors that path down  any items identified as directories, record their details, and then explore any further directories until there are no more.. which is probably inherently a bad plan.

    Perhaps you should come up with an application to do this for you, that can pipe back the data to SQL Server instead?


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Thursday, January 21, 2016 8:32 PM
  • If you want subfolders you can use "DIR /S".  However, that will show a header for each subdirectory.

    Thursday, January 21, 2016 8:35 PM
  • Quick Google search returned this thread

    http://stackoverflow.com/questions/11559846/how-to-list-files-inside-a-folder-with-sql-server

    Looks like it has several solutions.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, January 21, 2016 8:41 PM
  • Hi Swetha,

    As xp_cmdshell is not encouraged for its security issue, besides the CLR procedure, you can output all the files and the modified date and time into a flat file by running the below script in a command window.

    forfiles /p "thefolderpath" /c "cmd /c echo @path @file @fdate @ftime >>d:\destination.txt" /m *.* /s


    Then you can bulk import the destination.txt using T-SQL.

    If you have any question, feel free to let me know.

    Eric Zhang
    TechNet Community Support

    • Proposed as answer by Naomi N Friday, January 22, 2016 4:31 PM
    • Marked as answer by Swetha Monpara Monday, January 25, 2016 3:29 PM
    Friday, January 22, 2016 2:35 AM
  • Thank you everyone for the response.

    Monday, January 25, 2016 3:29 PM
  • Hi Swetha Monpara,

    It's run ok with me. Thank you so much.

    One more question. Do you have any way to import files from other machine folders shared via Lan network?

    Ex: DECLARE @cmd VARCHAR(8000) = 'dir \\ip add\Data\Folder_need_Import'

    Regards,

    Monday, November 11, 2019 10:18 AM
  • Dear all,

    It's done when I used hostname :D

    DECLARE @cmd VARCHAR(8000) = 'dir \\company.abc\Data\Folder_need_Import'

    Rgs,

    Tuesday, November 12, 2019 2:41 AM