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

Wivinia

Why don't you keep the documents in the database? – Orphism

@Orphism Thanks. the image path exists in the data. – Wivinia

I understand. Why don't you keep the documents in the database? – Orphism

@Orphism Actually file information is not required on regular basis & this may be the first & last time we need to know the file size. Just to update you i found the solution, have you seen it? please find my answer here – Wivinia

Very nice, but I don't know if it's good design these days to keep documents on a file system and only their paths in the database. – Orphism

Just to update you that why we are doing this: 1- Our DB size is in GBs. 2- Imagefolder size is nearly 5GB & is increasing day by day. 3- The same DB is used by more than one websites. 4- The same DB is used by nearly 50 users at a time inside our office 5- We have a lot of customers & daily thousands of customer visits our sites & can also access data. – Wivinia

Answer 1

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!!!

Wivinia

I'm not sure anything using xp_cmdshell can be called "perfect" – Concepcion

I'm not sure anything using WHILE-loops can be called "perfect" – Widower

Did not work for me. Got an conversion error on line 94 ( CAST(CAST(FileSize AS DECIMAL(13,2))/ @1MB AS DECIMAL(13,2)), ). Seems the DIR on my server is different. – Shoffner

Msg 8114, Level 16, State 5, Procedure GetListOfFileWithSize, Line 97 Error converting data type varchar to numeric. – Feltonfelts

@yaqub Ahmad, there errors in the script, it creates a physical table output, change it to # one also , this table is not declare #tempFileInfo I think it should be changed to #tempFileInformation – Hammett

@Bijujose Its already declared, see this line of code "CREATE TABLE #tempFileInformation"| – Wivinia

@YaqubAhmad, check the line no:85 and 90. if you copy paste the script to SSMS. This is not declared I believe this should be #tempfileinformation – Hammett

Answer 2

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%'

Deplume

Thanks. The 'size(bytes)' always returns NULL. Have you noticed it? – Wivinia

Should only return null for directories – Deplume

This solution is locale dependent, I had to modify the select like this: SELECT convert(datetime, LEFT(returnval,17),104) AS file_date, CAST( LTRIM(RTRIM(REPLACE(SUBSTRING(returnval,20,17),' ',''))) AS bigint) AS size, SUBSTRING(returnval,37, 50) AS [file_name] – Feltonfelts

Answer 3

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

Equal

THANKS @EI Ronnoco I was trying this but i am afraid that it may not be the solution i am looking for because the folder contains thousands of image files. – Wivinia

This procedures works fine but only for limited files... Any idea how to solve this issue? It just returns null after like 100 files. – Ahasuerus

Answer 4

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.

Underhung

Thanks. To store the image size & location in the database is a great idea, we will try to implemented it in future. – Wivinia

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;

Mirthless

Answer 6

Version 2.0 of perfect solution!!

-- =============================================
-- Author:      Carlos Dominguez ([email protected])
-- 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

Nealy