SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to calculate the size of a folder on cdrive
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cplusplus
Aged Yak Warrior

534 Posts

Posted - 11/13/2012 :  17:06:00  Show Profile  Reply with Quote
Is it possible to calculate the size of the folder via sql server, it has files and subfolders both.

"c:\PBAProj"

Like the above i have almost 20 folders which i need to calculate and provide teh details as a report.

Thanks a lot for the helpful info.

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 11/13/2012 :  18:01:37  Show Profile  Reply with Quote
Here is one way:

EDIT: Replace <dir> with your directory you want to check on.

DECLARE @Foo TABLE (Val NVARCHAR(4000))

INSERT @Foo
exec xp_cmdshell N'dir "<dir>" /s /-c /a | find "bytes" | find /v "free"'

SELECT 
   SUM(CAST(LEFT(
            SUBSTRING(Val, PATINDEX('%)%', Val) + 1, LEN(Val))
            ,LEN(SUBSTRING(Val, PATINDEX('%)%', Val) + 1, LEN(Val))) - 5
        )AS BIGINT)) AS TotalBytes
FROM @Foo

PS - You may need to enable xp_cmdshell

USE master
GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE;

Edited by - Lamprey on 11/13/2012 18:03:21
Go to Top of Page

cplusplus
Aged Yak Warrior

534 Posts

Posted - 11/13/2012 :  20:46:17  Show Profile  Reply with Quote
Thanks a lot Lamprey,

If i want to check multiple directories in once go, how can i do that.

example:
"c:\myproj1"
"c:\myproj2"
"c:\myproj3"

Thanks a lot for the helpful info.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/14/2012 :  08:30:56  Show Profile  Reply with Quote
You can make the snippet Lamprey posted into a stored proc and call it repeatedly. Instead of a table variable, create a temp table before calling the stored procs and insert into the stored proc.

An alternative, using Powershell is as follows. I must admit, this is a bit convoluted - usually people use Powershell to run SQL commands (or other commands), here I am using SQL to run xp_cmdshell to run Powershell. Weired, eh?
EXEC xp_cmdshell 
	N'Powershell -Command "&{$dirs =  	@(		\"C:\Temp\",			\"C:\Tests\"		);		foreach($d in $dirs) 	{$total=0; Get-ChildItem -recurse $d | % { $total += $_.length } ; \"$d   $total\";}}'
Be sure to put the folder names in double-quotes escaped with a backslash.

Edited by - sunitabeck on 11/14/2012 08:32:37
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/14/2012 :  08:37:14  Show Profile  Reply with Quote
The code in my previous posting came out in a weird format. What I meant is that the code should be like shown below EXCEPT, at the end of each line, you need a backslash (\). But if I put the backslash into the posting, it shows up weird as above.
EXEC xp_cmdshell 
	N'Powershell -Command "&{$dirs =
	@(
		\"C:\Temp\",	
		\"C:\Tests\"
	);	
	foreach($d in $dirs) 
	{$total=0; Get-ChildItem -recurse $d | % { $total += $_.length } ; \"$d   $total\";}}'
Go to Top of Page

cplusplus
Aged Yak Warrior

534 Posts

Posted - 11/14/2012 :  09:21:43  Show Profile  Reply with Quote
Thanks a lot for the help Lamprey , Sunitha.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000