Calculating Folder Size in TSQL

I am looking for a method to calculate the size of a directory within
a stored procedure/function.
Basically, I want to do a select on a table, grab all data from the
table and get the size of a directory for each record. The select
would look something like this...
SELECT
w.WebsiteName,
w.IPAddress,
w.Status,
c.ClientName,
dbo.udfGetFolderSize(w.WebsitePath) as FolderSize -- <-- This is
what I am looking for...
FROM dbo.Websites w
INNER JOIN dbo.Clients c
ON c.ID = w.Clients_ID
ORDER BY w.WebsiteName, C.ClientNameHi,
I'm doing something similar with the DOS dir command and xp_cmdshell, though
I'm looking for files, not total usage.
Here is a small example to get you started.
Good luck,
Jay
-- cut --
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 @.path = 'C:\WINDOWS'
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)'
-- cut --
<TampaWebDevelopment@.gmail.com> wrote in message
news:1192614500.170755.290550@.q3g2000prf.googlegroups.com...
>I am looking for a method to calculate the size of a directory within
> a stored procedure/function.
> Basically, I want to do a select on a table, grab all data from the
> table and get the size of a directory for each record. The select
> would look something like this...
> SELECT
> w.WebsiteName,
> w.IPAddress,
> w.Status,
> c.ClientName,
> dbo.udfGetFolderSize(w.WebsitePath) as FolderSize -- <-- This is
> what I am looking for...
> FROM dbo.Websites w
> INNER JOIN dbo.Clients c
> ON c.ID = w.Clients_ID
> ORDER BY w.WebsiteName, C.ClientName
>|||The problem with this is enabling xp_cmdshell which is a potential security
threat. Why not use WMI with VBScript instead, write the results in a CSV
file and import the data in a SQL Server table?
"Jay" <spam@.nospam.org> wrote in message
news:OAfGOXMEIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I'm doing something similar with the DOS dir command and xp_cmdshell,
> though I'm looking for files, not total usage.
> Here is a small example to get you started.
> Good luck,
> Jay
> -- cut --
> 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 @.path = 'C:\WINDOWS'
> 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)'
> -- cut --
>
> <TampaWebDevelopment@.gmail.com> wrote in message
> news:1192614500.170755.290550@.q3g2000prf.googlegroups.com...
>>I am looking for a method to calculate the size of a directory within
>> a stored procedure/function.
>> Basically, I want to do a select on a table, grab all data from the
>> table and get the size of a directory for each record. The select
>> would look something like this...
>> SELECT
>> w.WebsiteName,
>> w.IPAddress,
>> w.Status,
>> c.ClientName,
>> dbo.udfGetFolderSize(w.WebsitePath) as FolderSize -- <-- This is
>> what I am looking for...
>> FROM dbo.Websites w
>> INNER JOIN dbo.Clients c
>> ON c.ID = w.Clients_ID
>> ORDER BY w.WebsiteName, C.ClientName
>|||He asked for a way from within T-SQL. Can you use WMI from SQL? I didn't
think so.
"bass_player [SBS-MVP]" <bass_player@.mvps.org> wrote in message
news:u2LkjuVEIHA.1208@.TK2MSFTNGP05.phx.gbl...
> The problem with this is enabling xp_cmdshell which is a potential
> security threat. Why not use WMI with VBScript instead, write the results
> in a CSV file and import the data in a SQL Server table?
> "Jay" <spam@.nospam.org> wrote in message
> news:OAfGOXMEIHA.5208@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I'm doing something similar with the DOS dir command and xp_cmdshell,
>> though I'm looking for files, not total usage.
>> Here is a small example to get you started.
>> Good luck,
>> Jay
>> -- cut --
>> 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 @.path = 'C:\WINDOWS'
>> 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)'
>> -- cut --
>>
>> <TampaWebDevelopment@.gmail.com> wrote in message
>> news:1192614500.170755.290550@.q3g2000prf.googlegroups.com...
>>I am looking for a method to calculate the size of a directory within
>> a stored procedure/function.
>> Basically, I want to do a select on a table, grab all data from the
>> table and get the size of a directory for each record. The select
>> would look something like this...
>> SELECT
>> w.WebsiteName,
>> w.IPAddress,
>> w.Status,
>> c.ClientName,
>> dbo.udfGetFolderSize(w.WebsitePath) as FolderSize -- <-- This is
>> what I am looking for...
>> FROM dbo.Websites w
>> INNER JOIN dbo.Clients c
>> ON c.ID = w.Clients_ID
>> ORDER BY w.WebsiteName, C.ClientName
>>
>