Finding the total size of databases on SQL Server 2000 and SQL Server 2005
Is there a quick way to find the total size of each database on SQL Server 2000 and SQL Server 2005? I have tried running “sp_spaceused”, but I do not want to run it manually for each database. Do you have any ideas?
Software/Hardware used:
Software/Hardware used:
ASKED:
July 1, 2008 8:09 PM
UPDATED:
July 1, 2008 11:19 PM
Answer Wiki:
You can run this commend for each database.
<pre>select sum(convert(dec(15), size))
from dbo.sysfiles</pre>
To automate it for all databases, you'll want something like this.
<pre>create table #c
(db sysname,
size dec(15))
declare @b numeric(12,2)
select @b = low
from master.dbo.spt_values
where number = 1
and type = 'E'
exec sp_MSforeachdb '
use ?
insert into #c
select db_name(), sum(convert(dec(15), size))
from dbo.sysfiles'
select db, size/(1048576/@b)
from #c
drop table #c</pre>
To see all answers submitted to the Answer Wiki: View Answer History.
Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.