Yesterday, I was tasked w/ finding and identify the filesize of some files located in some directory. SQL doesn’t really have a function to do this and neither does a straight up DOS’ dir command.

Forunately, the Internet is a big place and someone wrote a one line syntax to do what I needed.

@echo off
for %%a in (E:\path\to\files\*.*) do (if %%~za GEQ 0 (echo %%~za %%a) ) )

I took this code, put it into a DOS .bat file, output the call to a .txt file, and then finally bulk inserting it into a table for SQL to crunch thru. With over 300GB of files in my directory, it took roughly 18seconds to complete the entire process.

I made some changes to what files were being stored in my directory and cut the process down to less than a second.

set @cmdListing = '\\path\to\batch\file\filesize_listing.bat > \\path\to\output_listing.txt'

exec xp_cmdshell @cmdListing

bulk insert dbname..fileSizesTable from 'E:\path\to\output_file\output_listing.txt'

After this, it was basically a select statement to find out if my kb column in the fileSizesTable column contains any 0.

Recent Entries

• C# - initialize a variable
• Return a status or value from sp_start_job
• iPhone 3G
• Creating Windows Clustered Resources
• changing sql services password
• sql 2005 connection error
• finding a bunch of server’s ip address
• finding physical servername
• order by asc for two (2) columns
• Finding filesizes

This entry was posted on Friday, February 6th, 2009 at 12:34 pm and is filed under sql. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.