Drive capacity

Hi folks,
I need to find out Drive capacity and free space, I do not have access
to Windows. I just have sa access to the SQL Server. I used
xp_fixeddrives to find out the free space, but how do I know the
capacity of the drive.
Thanks in advance.
--
*** Sent via Developersdex http://www.examnotes.net ***Well, if you are using Windows Server 2003, you can use wmic for this.
SET NOCOUNT ON;
CREATE TABLE #drives
(
drive CHAR(2),
MBFree BIGINT
);
CREATE TABLE #scratch
(
lineitem NVARCHAR(2048)
);
INSERT #drives EXEC master..xp_fixeddrives;
-- might be a good idea to call wmic manually once first
-- to make sure it is installed and loaded
INSERT #scratch EXEC master..xp_cmdshell 'wmic volume list'
SELECT d.drive, s.Capacity, d.MBFree,
[%free] = CONVERT(DECIMAL(5,2), d.MBFree / s.Capacity * 100)
FROM #drives d INNER JOIN (
SELECT capacity = CONVERT(DECIMAL(10,2), CONVERT(BIGINT,
SUBSTRING(lineitem, 23, 13)) / 1024 / 1024.0),
drive = REPLACE(SUBSTRING(lineitem, 112, 2), ':', '')
FROM #scratch
WHERE SUBSTRING(lineitem, 125, 1) = '3'
) s ON d.drive = s.drive
ORDER BY 1;
DROP TABLE #drives, #scratch;
Else, I would use something outside of SQL Server. E.g. you can get this
information from scripting.filesystemobject in a VBS script, called through
wscript and scheduled through windows task scheduler, and stuff its output
into the database, instead of expecting the database to do it internally...
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:OxTikU2FGHA.2912@.tk2msftngp13.phx.gbl...
> Hi folks,
> I need to find out Drive capacity and free space, I do not have access
> to Windows. I just have sa access to the SQL Server. I used
> xp_fixeddrives to find out the free space, but how do I know the
> capacity of the drive.
> Thanks in advance.
> --
> *** Sent via Developersdex http://www.examnotes.net ***|||Wow!!! This is what I was looking for. Thanks a lot Aaron. You are
great.
*** Sent via Developersdex http://www.examnotes.net ***