| Author |
 Topic  |
|
|
CanadaDBA
Aged Yak Warrior
Canada
583 Posts |
 Posted - 02/12/2007 : 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)
USA
6997 Posts |
Posted - 02/12/2007 : 14:36:49
|
Size is the third column in sys.sysfiles
CODO ERGO SUM |
 |
|
|
CanadaDBA
Aged Yak Warrior
Canada
583 Posts |
Posted - 02/12/2007 : 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
USA
320 Posts |
Posted - 02/12/2007 : 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. |
Edited by - pareshmotiwala on 02/12/2007 14:54:57 |
|
|
|
CanadaDBA
Aged Yak Warrior
Canada
583 Posts |
Posted - 02/12/2007 : 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
USA
35007 Posts |
Posted - 02/12/2007 : 15:01:11
|
Just run sp_databases.
Tara Kizer |
|
|
|
RussStarksen
Starting Member
1 Posts |
Posted - 11/14/2012 : 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; :-) |
|
|
| |
Topic |
|
List databases with their size
