sql server - 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:myImageFolderimage1.png    //'myImageFolder' have image
c:myImageFolderFolder1imagex.png // 'myImageFolder' have another folder inside which is 'Folder1'.
c:myImageFolderFolder1ChildFolder1imagen.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

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.