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.
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 sysdatabasesSELECT * 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.sysfilesCODO 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 EndGoset nocount offcreate 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_helpdbSelect name_col,db_size from #db_config set nocount onYou 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 bwhere a.database_id=b.database_idgroup by a.name,a.compatibility_level--,b.database_id/* Russ */ Select * from Life; :-) |
|
|
|
|
|
|
|