how to find folder size using sql scripts?

Question

0.00/5 (No votes)

See more:

SQL-server-2000

SQL-Server-2005

, +

how to find folder size using sql scripts?

[OP's answer moved to question]
Hi here is the code to find.

but the catch is with this code i can find size of files inside the folder. Not the sum of all files and subfolder size.and if i have sublfoders not an indivdual files then it shows 0 bytes.

May i known why and how to calculate size of folder including subfolder size.

use tempdb
set nocount on
 
declare @line varchar(255)
declare @path varchar(255)
declare @command varchar(255)
 
if object_id('#output') is not null and objectproperty(object_id('#output'),
'IsTable') = 1
drop table #output
 
create table #output (line varchar(255))
 
set @path = 'C:\Program Files'
set @command = 'dir "' + @path +'"'
 
insert into #output
exec master.dbo.xp_cmdshell @command
 
select @line = ltrim(replace(substring(line, charindex(')', line)+1,len(line)), ',', ''))
from #output where line like '%File(s)%bytes'
 
print @line + ' (This is a string and the value ignores the size of any sub-folders)'

Posted 23-Jun-11 4:03am

kin karthik

Updated 27-Jun-11 4:25am

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

fjdiewornncalwe · Answer 1 · 2011-06-23T04:07:00

A simple google search using a cut-and-paste of your question text returned about 2.6 million results.
I found an answer to your question in the first 10 results.

how to find folder size using sql scripts[^]

from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=156137[^]:
"You can use xp_cmdshell to call the dir command with the appropriate flags, or maybe a Powershell script, but that's about it. There are no native SQL commands to provide that information."

[edit]
As per your additional information, I am not entirely familiar with doing this, but you may need to create a process that will create a running tally of the sum of file sizes and then iterate through each sub folder. This would be a very inefficient and time consuming process if the directory structure gets to any substantial size, so hopefully someone can provide you better info as well.
[/edit]