Find size of multiple databases in SQL Server 2005

Question

I was wondering if there is a sql statement to get the current size of all the databases on your server instead of right clicking and going to properties for each one.

Answer 1

Check out the sys.master_files table.

This query will give you total size of everything in your instance:

SELECT SUM(size*8192.0) AS totalsize
FROM sys.master_files;

Answer 2

If you can use stored procedures, this should work:

exec sp_msforeachdb 'use ? exec sp_spaceused'

Answer 3

The simplest way is to use:

exec [sys].[sp_databases]
go

Which produces something like:

Name        Size  Remarks
mydatabase1 29888 NULL
mydatabase2 13760 NULL
mydatabase3 11776 NULL
master      5376  NULL
model       3008  NULL
msdb        7616  NULL
tempdb      2560  NULL

For the size of the transaction logs associated with a database, use:

dbcc sqlperf(logspace)
go

Which produces something like:

Name        Log Size (MB) Log Space Used (%) Status
master      1.242188      50.9434            0
tempdb      0.7421875     61.25              0
model       0.7421875     38.94737           0
msdb        1.992188      35.88235           0
mydatabase1 5.554688      18.55661           0
mydatabase2 2.742188      32.9594            0
mydatabase3 8.992188      26.58015           0

Answer 4

exec sp_helpdb

Lists out the size of data, owner, creation date, etc for all databases on a server in one nice table.

If you then want to go delving into a particular database to see the individual table sizes you can use

use MyFunkyDb
go
EXECUTE sp_MSforeachtable 'EXECUTE sp_spaceused [?]'