Find the size of multiple databases in SQL Server 2005

Question

I was wondering if there is an sql instruction to get the current size of all the databases on your server, rather than right-clicking and going to the properties for each of them.

Answer 1

If you can use stored procedures, this should work:

exec sp_msforeachdb 'use ? exec sp_spaceused'

Answer 2

Check out the sys.master_files table .

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

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

Answer 3

- :

exec [sys].[sp_databases]
go

- :

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

, , :

dbcc sqlperf(logspace)
go

- :

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 data, owner, creation date, etc. for all databases on the server in one beautiful table.

If you want to go to a specific database to see the individual table sizes, you can use

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