View mdf size of dbs in SQL 2005

Hello there,
I have a instance in SQL 2005 Ent with 32 DB;s and I need to know the sql script/command that I can run to display size of mdf's of each of these DB's.
Please advise.
Thanks

goprasadAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
subhashpuniaConnect With a Mentor Commented:
You can get the result in temp table and see in better way as below:

create table #DataFileSize (DBName VARCHAR(1000), Size_KB int, FilePath varchar(4000))
insert into #DataFileSize
exec sp_msforeachdb 'use [?]; select db_name(), size*8 as ''in KB'', filename from sysfiles where filename like ''%.mdf'''
select DBName, FilePath, Size_KB/1024 As FileZise_MB from #DataFileSize
drop table #DataFileSize
0
 
mkobrinCommented:
run the sp_databases stored procedure on the master data base. It will supply you with the info you need
0
 
cyberkiwiCommented:
For just the .mdf files (not including log files or .ndf secondary files)

exec sp_msforeachdb 'use ?; select db_name(), size*8 as ''in KB'', filename from sysfiles where filename like ''%.mdf'''

For all data files (non log)

exec sp_msforeachdb 'use ?; select db_name(), size*8 as ''in KB'', filename from sysfiles where groupid>0'
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
subhashpuniaCommented:
a simple command is:

sp_msforeachdb 'sp_helpdb [?]'
0
 
AvalonSACommented:
I believe that the most easy way is:
select db.name, mf.physical_name, mf.size*8 as "KB"
from sys.master_files mf join sys.databases db
on mf.database_id = db.database_id
where physical_name like '%.mdf'

Data files are not only mdf, each additional is called .ndf or you can specify whatever you want so to be sure you can execute this (Retrieve the datafiles size of all DB data files):

select db.name, mf.physical_name, mf.size*8 as "KB"
from sys.master_files mf join sys.databases db
on mf.database_id = db.database_id
where mf.type_desc = 'ROWS'
0
 
goprasadAuthor Commented:
Solved my problem, great sql command and subsequent output.
0
All Courses

From novice to tech pro — start learning today.