使用SQL获取文件夹内的每个文件大小 [英] Get each file size inside a Folder using SQL

问题描述

我们正在保存图像文件夹，可以有图像&子文件夹&这些子文件夹也可以具有图像&子文件夹，例如

  c：\myImageFolder\image1.png //'myImageFolder'有图像
c： \myImageFolder\Folder1\imagex.png //myImageFolder有另外一个文件夹，里面是Folder1。 
c：\myImageFolder\Folder1\ChildFolder1\imagen.png //'myImageFolder'有'Folder1'有'ChildFolder1'有imagen.png 
  

我们需要知道有多少图片超过1 MB，超过750KB和500KB？

<一些事实：

• 我们需要通过SQL来完成
  
• 我们使用SQL Server 2008


• myImageFolder 包含超过数千个子文件夹


• myImageFolder 尺寸接近5 GB

预先感谢您的宝贵时间&帮帮我。
注意： 我找到了解决方案，你可以在这里找到它 这个完美的解决方案：

  ALTER PROCEDURE [dbo]。[GetListOfFileWithSize] 
（
 @Dir VARCHAR（1000）
）
 AS 
 --------------------------- -------------------------------------------------- ---------------- 
  - 变量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 
 
 ------------------------------------ - -------------------------------------------------- ----- 
  - 创建临时表
 -------------------------------- -------------------------------------------------- ----------- 
 CREATE TABLE #diot（DIRID int identity（1,1），directory varchar（400））
 CREATE TABLE #tempoutput（line varchar（400）） 
 CREATE TABLE输出（Directory varchar（400），FilePath VARCHAR（400），SizeInMB DECIMAL（13,2），SizeInKB DECIMAL（13,2））
 
 CREATE TABLE #tempFilePaths VARCHAR（500））
 CREATE TABLE #tempFileInformation（FilePath VARCHAR（500），FileSize VARCHAR（100））
 
 ---------------- -------------------------------------------------- --------------------------- 
  - 调用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 =（sel （*）from #dirs）
 
 --------------------------------- -------------------------------------------------- ---------- 
  - 处理返回数据
 -------------------------- -------------------------------------------------- ----------------- 
 
 WHILE @Counter<> 0 
 BEGIN 
 DECLARE @filesize INT 
 SET @curdir =（SELECT directory FROM #dirs WHERE DIRID = @counter）
 SET @command ='dir''+ @curdir + '''
 ------------------------------------------- ----------------------------------------------- 
  - 清空表格
 DELETE FROM #tempFilePaths 
 
 
 INSERT INTO #tempFilePaths 
 EXEC MASTER..XP_CMDSHELL @command 
 
  - 删除所有目录
 DELETE #tempFilePaths WHERE文件LIKE'％< dir>％'
 
  - 删除所有的信息消息
 DELETE #tempFilePaths WHERE文件LIKE'％'
 
  - 删除空值
 DELETE #tempFilePaths WHERE文件IS NULL 
 
  - 删除dateinfo 
 UPDATE #tempFilePaths SET文件s = RIGHT（files，（LEN（files）-20））
 
  - 删除前导空格
 UPDATE #tempFilePaths SET files = LTRIM（files）
 
  - 将数据分割成大小和文件名
 ------------------------------------ ---------------------- 
  - 清除表
 DELETE FROM #tempFileInformation; 
 
  - 存储FileName&大小
 INSERT INTO #tempFileInformation 
 SELECT 
 RIGHT（files，LEN（files）-PATINDEX（'％％'，files））AS FilePath，
 LEFT（files，PATINDEX '％％'，files））AS FileSize 
 FROM #tempFilePaths 
 
 ------------------------- ------- 
  - 删除逗号
 UPDATE #tempFileInformation 
 SET FileSize = REPLACE（FileSize，'，'，''）
 
 
 
 ------------------------------------------- ------------------- 
  - 将结果存储在输出表
 -------------- ------------------------------------------------ 
 
 INSERT INTO输出 - （FilePath，SizeInMB，SizeInKB）
 SELECT 
 @curdir，
 FilePath，
 CAST（C AST（FileSize AS DECIMAL（13,2））/ @ 1MB AS DECIMAL（13,2）），
 CAST（CAST（FileSize AS DECIMAL（13,2））/ @ 1KB AS DECIMAL（13,2） ）
 FROM #tempFileInformation 
 
 ---------------------------------- -------------------------------------------------- -------- 
 
 
设置@counter = @counter -1 
 END 
 
 
从输出中删除目录为空
 -------------------------------------------- -  
  -  DROP临时表
 ----------------------------------- ----------- 
 DROP TABLE #Tempoutput 
 DROP TABLE #dirs 
 DROP TABLE #tempFilePaths 
 DROP TABLE #tempFileInformation 
 --DROP TABLE #tempfinal 
 
 
 SELECT * FROM OutPut 
 DROP TABLE输出
  

和家伙的作品!!!

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

解决方案

Check this perfect 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, ',','')



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

这篇关于使用SQL获取文件夹内的每个文件大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！