How do list files ,date and size inside a folder using SQL Server


In SQL server every day we face issues where we want list of files from there size , date from sql server.

Below is the script to get same but make sure xp_cmdshell is enabled
Quick Download
/************ Script to list files , size , date in specific folder**************/

DECLARE @cmd varchar(100)
DECLARE @path varchar(100)

SET @Path = 'c:\Windows\' --> Pass the path in which you want to search
SET @cmd = 'dir ' + @path + ' /A /OS /-C'

CREATE Table #MyTable (Results varchar(500))

INSERT INTO #MyTAble
EXEC XP_CMDSHELL @cmd

SELECT LEFT(Results, 20) [create_date],
CASE LTRIM(RTRIM(SUBSTRING(Results, 21, 18))) 
WHEN '' THEN 1 ELSE 0 END as is_directory,  
CASE LTRIM(RTRIM(SUBSTRING(Results, 21, 18))) 
 WHEN '' THEN NULL ELSE LTRIM(RTRIM(SUBSTRING(Results, 21, 18)))  END as size_in_bytes,  
SUBSTRING(Results, 40, Len(Results)) AS [name]
FROM #MyTable
WHERE ISNULL(PATINDEX('%__/__/____%', Results), 0) != 0

DROP TABLE #MyTable

Reference : http://www.sqlservercentral.com/articles/SQLCLR/65656/

3 comments:

  1. UnknownDecember 24, 2014 at 5:26 AM

    This comment has been removed by the author.

    ReplyDelete
  2. UnknownDecember 24, 2014 at 5:29 AM

    Hi Saurabh,

    I need your help...

    Need SQL code to find the following details in a Drive/Path: - Folder Name and File Name - Folder Size and File Size in MB - Date Created and Date Modified and Date Accessed - File Flag (1/0) Yes or no based on whether the line item is a file or a folder - Path/URL/Location of the file or folder Need the details for all the folders and sub-folders and files in a given Path/URL

    I have multiple codes which provides each of these details individually, but unable to merge it in a single query or join post execution.

    Expected output:

    http://s6.postimg.org/cn845zwdd/expected_output.jpg

    Parts of the Codes are available in the Blogs as below:

    http://sqljourney.wordpress.com/2010/06/08/get-list-of-files-from-a-windows-directory-to-sql-server/#comment-1674 (This link provides a function that gives date modified/path)

    http://stackoverflow.com/questions/7952406/get-each-file-size-inside-a-folder-using-sql

    (this link provides file size, file flag)

    The above links provides parts of the query but i am unable to join them to get them in a single Store procedure..

    ReplyDelete
  3. ed medicationNovember 25, 2020 at 9:10 AM

    This piece of writing offers clear idea designed for the new viewers of blogging, that actually how to do running a blog.

    ReplyDelete

Subscribe to: Post Comments (Atom)