Get each file size inside a Folder using SQL

Question

We are keeping images in Folder which can have images & sub folder & these sub folders can also have images & sub folders for example

c:\myImageFolder\image1.png    //'myImageFolder' have image
c:\myImageFolder\Folder1\imagex.png // 'myImageFolder' have another folder inside which is 'Folder1'.
c:\myImageFolder\Folder1\ChildFolder1\imagen.png // 'myImageFolder' have 'Folder1' which have 'ChildFolder1' which have imagen.png

We need to know that how many images are in there over 1 MB, over 750KB and 500KB?

Some facts:

• We need to do it through SQL
• We are using SQL Server 2008
• myImageFolder contains more than thousands sub folders
• myImageFolder size is nearly 5 GB

Thanks in advance for your valuable time & help. Note: I found the solution, you can find it here

Answer 1

If security isn't a huge issue and you can enable xp_cmdshell on your sql instance, you can use the command shell directory listings to get the info. For example

Declare @Dir VARCHAR(256)
DECLARE @CMD VARCHAR(256)
SET @Dir = 'C:\myImageFolder\'
SET @CMD = 'DIR "'+@DIR+'" /A /S'

CREATE TABLE #tmp 
    (returnval NVARCHAR(500), rownum INT IDENTITY(1,1))

-- Populate Temp Table with the contents of the outfiles directory
    INSERT #tmp EXEC master..xp_cmdshell @cmd

-- Delete rows with no file information
    DELETE FROM #tmp WHERE returnval IS NULL
    DELETE FROM #tmp WHERE ISNUMERIC(LEFT(returnval,1))=0 AND returnval NOT LIKE '%Directory of%'
    DELETE FROM #tmp WHERE returnval LIKE '%<DIR>          .%'

-- Separate the output into its proper columns
    SELECT 
        rownum,
        (SELECT TOP 1 REPLACE(returnVal, ' Directory of ','') FROM #tmp t2 WHERE t2.rownum < t.rownum AND t2.returnval LIKE ' Directory of%' ORDER BY t2.rownum DESC) Directory,
        CAST(LEFT(returnval,10) AS DATETIME) AS file_date,
        CASE WHEN SUBSTRING(returnval,22,17) LIKE '%<DIR>%' THEN NULL ELSE CAST(REPLACE(SUBSTRING(returnval,22,17),',','') AS NUMERIC) END AS 'size(bytes)',
        RIGHT(RTRIM([returnval]),LEN(RTRIM([returnval]))-39) AS [file_name],
        CASE WHEN SUBSTRING(returnval,22,17) LIKE '%<DIR>%' THEN 'Directory' ELSE 'File' END AS [Type],
        CASE WHEN SUBSTRING(returnval,22,17) LIKE '%<DIR>%' THEN NULL ELSE RIGHT(rtrim([returnval]), CHARINDEX('.',REVERSE(RTRIM([returnval])))) END AS extension
    FROM #tmp t
    WHERE returnval NOT LIKE '%Directory of%'

Answer 2

I think you may be able to use sp_OAGetProperty. Something along the lines of ...

DECLARE @OLEResult INT
DECLARE @FS INT
DECLARE @FileID INT
DECLARE @Size BIGINT

-- Create an instance of the file system object
EXEC @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
EXEC @OLEResult = sp_OAMethod @FS, 'GetFile', @FileID OUT, 'C:\Filename'
EXEC @OLEResult = sp_OAGetProperty @FileID, 'Size', @Size OUT

--@Size now holds file size

You may need to use sp_configure to change the configuration option for 'Ole Automation Procedures'

Check out this link

Answer 3

You can create a c# function and add it to your SQL Server 2008 database and call the function from inside of SQL. Either a CLR Stored Procedure, or a CLR function would work fine for your scenario.

Creating CLR Stored Procedures - MSDN

Or, what you could also do (which makes more sense to me, but would take more work)... how does your program upload files? - Tap into that routine and also create an entry in the database that indicates its size and location.

Answer 4

Version 2.0 of perfect solution!!

-- =============================================
-- Author:      Carlos Dominguez (krlosnando@gmail.com)
-- Create date: July 07th 2017
-- Description: Scan folders and files Size
-- Example: EXEC [dbo].[spScanFolder] 'C:\Users\Public'
-- =============================================
CREATE PROCEDURE [dbo].[spScanFolder] 
(
    @FolderToScan VARCHAR(1000)
)
AS
BEGIN
    ---------------------------------------------------------------------------------------------
    -- Variable declaration
    ---------------------------------------------------------------------------------------------
    DECLARE @CurrentDir VARCHAR(400)
    DECLARE @Line VARCHAR(400)
    DECLARE @Command VARCHAR(400)
    DECLARE @Counter int

    DECLARE @1MB DECIMAL
    SET @1MB = 1024 * 1024

    DECLARE @1KB DECIMAL
    SET @1KB = 1024 

    ---------------------------------------------------------------------------------------------
    -- DROP temp tables
    ---------------------------------------------------------------------------------------------
    IF OBJECT_ID(N'tempdb..#tableTempDirs') IS NOT NULL BEGIN  DROP TABLE #tableTempDirs END
    IF OBJECT_ID(N'tempdb..#tableTempOutput') IS NOT NULL BEGIN  DROP TABLE #tableTempOutput END
    IF OBJECT_ID(N'tempdb..#tableTempResult') IS NOT NULL BEGIN  DROP TABLE #tableTempResult END
    IF OBJECT_ID(N'tempdb..#tableTempFilePaths') IS NOT NULL BEGIN  DROP TABLE #tableTempFilePaths END
    IF OBJECT_ID(N'tempdb..#tableTempFileInfo') IS NOT NULL BEGIN  DROP TABLE #tableTempFileInfo END

    ---------------------------------------------------------------------------------------------
    -- Temp tables creation
    ---------------------------------------------------------------------------------------------
    CREATE TABLE #tableTempDirs (DIRID int identity(1,1), directory varchar(400))
    CREATE TABLE #tableTempOutput (line varchar(400))
    CREATE TABLE #tableTempResult (Directory varchar(400), FilePath VARCHAR(400), SizeInMB DECIMAL(13,2), SizeInKB DECIMAL(13,2))
    CREATE TABLE #tableTempFilePaths (Files VARCHAR(500))
    CREATE TABLE #tableTempFileInfo (FilePath VARCHAR(500), FileSize VARCHAR(100))

    ---------------------------------------------------------------------------------------------
    -- Call xp_cmdshell
    ---------------------------------------------------------------------------------------------    
    SET @Command = 'dir "'+ @FolderToScan +'" /S/O/B/A:D'
    INSERT INTO #tableTempDirs EXEC xp_cmdshell @Command
    INSERT INTO #tableTempDirs SELECT @FolderToScan
    DELETE FROM #tableTempDirs WHERE Directory is null   

    ---------------------------------------------------------------------------------------------
    -- Remove text to extract file information from command result "05/27/2017  12:26 PM 5,208 rulog.txt"
    ---------------------------------------------------------------------------------------------      
    SET @Counter = (select count(*) from #tableTempDirs)
    WHILE @Counter <> 0
    BEGIN
        DECLARE @filesize INT
        SET @CurrentDir = (SELECT directory FROM #tableTempDirs WHERE DIRID = @Counter)
        SET @Command = 'dir "' + @CurrentDir +'"'

        -- Clear the table
        TRUNCATE TABLE #tableTempFilePaths

        -- Get files from current directory
        INSERT INTO #tableTempFilePaths
        EXEC MASTER..XP_CMDSHELL @Command 

        --delete all directories
        DELETE #tableTempFilePaths WHERE Files LIKE '%<dir>%'

        --delete all informational messages
        DELETE #tableTempFilePaths WHERE Files LIKE ' %'

        --delete the null values
        DELETE #tableTempFilePaths WHERE Files IS NULL

        --delete files without date "05/27/2017  12:26 PM 5,208 rulog.txt"
        --Fix error: Invalid length parameter passed to the right function.
        DELETE #tableTempFilePaths WHERE LEN(files) < 20

        --get rid of dateinfo
        UPDATE #tableTempFilePaths SET files = RIGHT(files,(LEN(files)-20))

        --get rid of leading spaces
        UPDATE #tableTempFilePaths SET files =LTRIM(files)

        --split data into size and filename and clear the table
        TRUNCATE TABLE #tableTempFileInfo;

        -- Store the FileName & Size
        INSERT INTO #tableTempFileInfo
        SELECT  
            RIGHT(files,LEN(files) -PATINDEX('% %',files)) AS FilePath,
            LEFT(files,PATINDEX('% %',files)) AS FileSize
        FROM
            #tableTempFilePaths

        --Remove the commas
        UPDATE #tableTempFileInfo
        SET FileSize = REPLACE(FileSize, ',','')

        --------------------------------------------------------------
        -- Store the results in the output table
        -- Fix Error: conveting varchar to decimal
        --------------------------------------------------------------
        INSERT INTO #tableTempResult--(FilePath, SizeInMB, SizeInKB)
        SELECT  
            @CurrentDir,
            FilePath,
            CASE FileSize
                WHEN 'File ' THEN 0
                ELSE CAST(CAST(FileSize AS DECIMAL(13,2))/ @1MB AS DECIMAL(13,2))
            END,
            CASE FileSize
                WHEN 'File ' THEN 0
                ELSE CAST(CAST(FileSize AS DECIMAL(13,2))/ @1KB AS DECIMAL(13,2))
            END
        FROM    
            #tableTempFileInfo

        Set @Counter = @Counter -1
    END

    -- Remove null directories
    DELETE FROM #tableTempResult WHERE Directory is null       

    ----------------------------------------------
    -- Show result
    ----------------------------------------------           
    SELECT * FROM  #tableTempResult 

    ----------------------------------------------
    -- DROP temp tables
    ----------------------------------------------           
    IF OBJECT_ID(N'tempdb..#tableTempDirs') IS NOT NULL BEGIN  DROP TABLE #tableTempDirs END
    IF OBJECT_ID(N'tempdb..#tableTempOutput') IS NOT NULL BEGIN  DROP TABLE #tableTempOutput END
    IF OBJECT_ID(N'tempdb..#tableTempResult') IS NOT NULL BEGIN  DROP TABLE #tableTempResult END
    IF OBJECT_ID(N'tempdb..#tableTempFilePaths') IS NOT NULL BEGIN  DROP TABLE #tableTempFilePaths END
    IF OBJECT_ID(N'tempdb..#tableTempFileInfo') IS NOT NULL BEGIN  DROP TABLE #tableTempFileInfo END
END

Answer 5

Here is a solution using xp_cmdshell to run powershell that returns xml which can be parsed because why not.

set nocount on;

declare @path varchar(1000), @cmd varchar(2000);

-- File path
set @path = 'c:\temp';
-- Powershell command to get a directory listing and output in to its clixml for parsing
set @cmd = 'powershell.exe -noprofile -outputformat xml -command "get-childitem -path ''' + @path + ''' -File"';

-- output table for xp_cmdshell
create table #cmdOutput ( [output] varchar(max));

-- run powershell command and collect output
insert into #cmdOutput ( output ) exec sys.xp_cmdshell @cmd;

-- remove some values for paring xml, agg to a single string, cast as xml
with cte as ( select cast(string_agg(
                          iif(a.output like '%xmlns%', replace(a.output, 'xmlns="http://schemas.microsoft.com/powershell/2004/04"', ''), a.output), ''
                          ) as xml) myDoc from #cmdOutput a where a.output <> '#< CLIXML'
            )
-- select data out of xml 
select b.fileObj.value('(./Props/S)[1]', 'varchar(1000)') [Name]
  , b.fileObj.value('(./Props/I64)[1]', 'bigint') [Length]
  , b.fileObj.value('(./Props/S)[2]', 'varchar(1000)') DirectoryName
  , b.fileObj.value('(./Props/B)[1]', 'bit') IsReadOnly
  , b.fileObj.value('(./Props/B)[2]', 'bit') [Exists]
  , b.fileObj.value('(./Props/S)[3]', 'varchar(1000)') FullName
  , b.fileObj.value('(./Props/S)[4]', 'varchar(1000)') Extension
  , b.fileObj.value('(./Props/DT)[1]', 'datetime2') CreationTime
  , b.fileObj.value('(./Props/DT)[3]', 'datetime2') LastAccessTime
  , b.fileObj.value('(./Props/DT)[5]', 'datetime2') LastWriteTime
from cte a
cross apply a.myDoc.nodes('/Objs/Obj') as b(fileObj)
where b.fileObj.value('(./Props/S)[1]', 'varchar(1000)') is not null


-- clean it up
drop table #cmdOutput;

Answer 6

Check this solution:

ALTER  PROCEDURE   [dbo].[GetListOfFileWithSize]  
(
    @Dir    VARCHAR(1000)
)
AS
---------------------------------------------------------------------------------------------
-- Variable decleration
---------------------------------------------------------------------------------------------
    declare @curdir nvarchar(400)
    declare @line varchar(400)
    declare @command varchar(400)
    declare @counter int

    DECLARE @1MB    DECIMAL
    SET     @1MB = 1024 * 1024

    DECLARE @1KB    DECIMAL
    SET     @1KB = 1024 

---------------------------------------------------------------------------------------------
-- Temp tables creation
---------------------------------------------------------------------------------------------
CREATE TABLE #dirs (DIRID int identity(1,1), directory varchar(400))
CREATE TABLE #tempoutput (line varchar(400))
CREATE TABLE output (Directory varchar(400), FilePath VARCHAR(400), SizeInMB DECIMAL(13,2), SizeInKB DECIMAL(13,2))

CREATE TABLE #tempFilePaths (Files VARCHAR(500))
CREATE TABLE #tempFileInformation (FilePath VARCHAR(500), FileSize VARCHAR(100))

---------------------------------------------------------------------------------------------
-- Call xp_cmdshell
---------------------------------------------------------------------------------------------    

     SET @command = 'dir "'+ @Dir +'" /S/O/B/A:D'
     INSERT INTO #dirs exec xp_cmdshell @command
     INSERT INTO #dirs SELECT @Dir
     SET @counter = (select count(*) from #dirs)

---------------------------------------------------------------------------------------------
-- Process the return data
---------------------------------------------------------------------------------------------      

        WHILE @Counter <> 0
          BEGIN
            DECLARE @filesize INT
            SET @curdir = (SELECT directory FROM #dirs WHERE DIRID = @counter)
            SET @command = 'dir "' + @curdir +'"'
            ------------------------------------------------------------------------------------------
                -- Clear the table
                DELETE FROM #tempFilePaths


                INSERT INTO #tempFilePaths
                EXEC MASTER..XP_CMDSHELL @command 

                --delete all directories
                DELETE #tempFilePaths WHERE Files LIKE '%<dir>%'

                --delete all informational messages
                DELETE #tempFilePaths WHERE Files LIKE ' %'

                --delete the null values
                DELETE #tempFilePaths WHERE Files IS NULL

                --get rid of dateinfo
                UPDATE #tempFilePaths SET files =RIGHT(files,(LEN(files)-20))

                --get rid of leading spaces
                UPDATE #tempFilePaths SET files =LTRIM(files)

                --split data into size and filename
                ----------------------------------------------------------
                -- Clear the table
                DELETE FROM #tempFileInformation;

                -- Store the FileName & Size
                INSERT INTO #tempFileInformation
                SELECT  
                        RIGHT(files,LEN(files) -PATINDEX('% %',files)) AS FilePath,
                        LEFT(files,PATINDEX('% %',files)) AS FileSize
                FROM    #tempFilePaths

                --------------------------------
                --  Remove the commas
                UPDATE  #tempFileInformation
                SET FileSize = REPLACE(FileSize, ',','')

                --------------------------------
                --  Remove the white space
                UPDATE  #tempFileInformation
                SET FileSize = REPLACE(FileSize, char(160) , '')

                --------------------------------------------------------------
                -- Store the results in the output table
                --------------------------------------------------------------

                INSERT INTO output--(FilePath, SizeInMB, SizeInKB)
                SELECT  
                        @curdir,
                        FilePath,
                        CAST(CAST(FileSize AS DECIMAL(13,2))/ @1MB AS DECIMAL(13,2)),
                        CAST(CAST(FileSize AS DECIMAL(13,2))/ @1KB AS DECIMAL(13,2))
                FROM    #tempFileInformation

            --------------------------------------------------------------------------------------------


            Set @counter = @counter -1
           END


    DELETE FROM OUTPUT WHERE Directory is null       
----------------------------------------------
-- DROP temp tables
----------------------------------------------           
DROP TABLE #Tempoutput  
DROP TABLE #dirs  
DROP TABLE #tempFilePaths  
DROP TABLE #tempFileInformation  
--DROP TABLE #tempfinal  


SELECT  * FROM  OutPut
DROP TABLE output 

And guys it works!!!