How to calculate the size of a folder on cdrive

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to calculate the size of a folder on cdrive

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2012-11-13 : 17:06:00

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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-11-13 : 18:01:37

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;

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2012-11-13 : 20:46:17

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.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-14 : 08:30:56

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.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-14 : 08:37:14

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\";}}'

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2012-11-14 : 09:21:43

Thanks a lot for the help Lamprey , Sunitha.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources