Hi,

I executed the below query and getting the capacity values only for master database.All other DB shows NULL values for spaceused.

I'm acutally looking for a query to get all the capacity information other than using temp table and the procedures.

Is there any way using SQL query ONLY.

select
db.[dbid] as 'DB ID',
db.[name] as 'Database Name',
af.[name] as 'Logical Name',
convert(decimal(12,2),round(size/128.000,2)) as FileSizeMB, 
convert(decimal(12,2),round(fileproperty(db.name,'SpaceUsed')/128.000,2)) as SpaceUsedMB
from sys.sysdatabases db
inner join sys.sysaltfiles af
on db.dbid = af.dbid

Below is the output

DB ID Database Name Logical Name FileSizeMB SpaceUsedMB
1 master master 4.00 3.44
1 master mastlog 2.00 3.44
2 tempdb tempdev 8.00 NULL
2 tempdb templog 0.50 NULL
3 model modeldev 2.19 NULL
3 model modellog 0.75 NULL
4 msdb MSDBData 14.13 NULL
4 msdb MSDBLog 0.75 NULL
5 ReportServer ReportServer 5.19 NULL
5 ReportServer ReportServer_log 6.88 NULL
6 ReportServerTempDB ReportServerTempDB 4.19 NULL
6 ReportServerTempDB ReportServerTempDB_log 1.06 NULL
7 MyAdventureWorks AdventureWorks2012_Data 205.00 NULL
7 MyAdventureWorks AdventureWorks2012_Log 1.00 NULL

Dont use sysaltfiles its deprecated. use sys.database_files or sys.master_files

Tried with sys.database_files--This only shows master db info,none of the other db capacities displayed.

sys.master_files shows all the db,but doesnt show values for Available Space except master DB,All other db shows NULL values

For your first script, you are using the FILEPROPERTY function incorrectly. It expects a filename, and you pass the database name. Also, it only work for the current database.

Sys.master_files do not have file size. And FILEPROPRTY will work just ad badly for sys.master_files as for master..sysaltfiles - it only work for current database.

So, you do need several queries, potentially encapsulated in a stored procedure. I use my own sp_dbinfo, but that do not show file size, only db (data and log separated):

http://www.karaszi.com/SQLServer/util_sp_dbinfo.asp

My use case is without using a stored procedure and creation of table.

By using ONLY SQL query is there any way to get these info