List databases with their size

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

List databases with their size

Author

Topic

CanadaDBA

583 Posts

Posted - 2007-02-12 : 14:27:51

I want to list my databases, and physical related files, and their size but the following commands do not provide the size. Any idea?


SELECT * FROM sysdatabases
SELECT * FROM sys.sysfiles

I know it's silly question

Canada DBA

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-12 : 14:36:49

Size is the third column in sys.sysfiles

CODO ERGO SUM

CanadaDBA

583 Posts

Posted - 2007-02-12 : 14:52:22

That size is in 8KB and is only for the DB being in USE. For example if the size has a value of 512, it means the file size is 4096 kb. Then I have to have a cursor to navigate all my databases.

Isn't there a table or view containing the databases and their files and sizes?

Canada DBA

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2007-02-12 : 14:53:49

if exists (select * from dbo.sysobjects where name = '#db_config' and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Begin
DROP TABLE #db_config
End
Go

set nocount off
create table #db_config
(
name_col varchar(30),
db_size varchar(20),
owner varchar(50),
dbid int,
created varchar(20),
status varchar(255),
compatibility_level varchar(20)

)
insert #db_config
exec sp_helpdb

Select name_col,db_size from #db_config

set nocount on

You may have to tweak this a bit because the sp_helpdb returns results with different datatypes and length in different versions.

CanadaDBA

583 Posts

Posted - 2007-02-12 : 14:58:22

Found:

SELECT Name [Database], Physical_Name [Physical file], size*8 [Size_KB] FROM sys.master_files

Thanks for all posts!

Canada DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-12 : 15:01:11

Just run sp_databases.

Tara Kizer

RussStarksen
Starting Member

1 Post

Posted - 2012-11-14 : 17:12:00

/* how about this query */
select @@servername,
a.name,
a.compatibility_level,
-- b.database_id [database_id],
SUM(((size*8)/1024)) [Size_MB]
FROM sys.databases a,sys.master_files b
where a.database_id=b.database_id
group by a.name,a.compatibility_level
--,b.database_id

/* Russ */ Select * from Life; :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources