Ever have a need to calculate the total available storage space for a server? Recently I did. Here's a solution I came up with - I bet someone can do this better!
There's a handy stored procedure called xp_fixeddrives that reports the available storage space: exec xp_fixeddrives
drive MB free
Problem solved right? Maybe.
What I really want is the sum total of all available space presented to the server. I built this to accomplish that:
declare @tbl table
First I built a table variable to hold the results of xp_fixeddrives. Then I populated it with an INSERT statement:
insert into @tbl
To test, I execute a SELECT statement:
select * from @tbl
Finally, I execute a SELECT SUM to gather the total storage space available:
select sum(MBFree) as ServerDriveSpace
The result is:
There are other ways to approach this. How would you do it?