sys.database_files JOIN for get all database

Question

Hi all,
I try with a single query to get all DB FreeSpaceMB... but is it possible?
I need one only query not temp or procedure... a classic t-sql statement.
I did some research online an not seems to me possible, is it right?

Every advise is welcome.
Alen , Italy

SELECT DB_NAME() AS DbName, 
    a.name AS FileName, 
    a.type_desc,
    a.size * 8 AS CurrentSizeKb,  
    a.size/128.0 AS CurrentSizeMB,  
    a.size * 8 - CAST(FILEPROPERTY(a.name, 'SpaceUsed') AS INT) * 8 AS FreeSpaceKb,
    a.size/128.0 - CAST(FILEPROPERTY(a.name, 'SpaceUsed') AS INT) / 128.0 AS FreeSpaceMB
FROM sys.database_files a

Answer 1

Hi，@Alen Cappelletti

Welcome to Microsoft T-SQL Q&A Forum!

The FILEPROPERTY you used is already the easiest function to obtain the remaining space. In my opinion, it is impossible to query the remaining space with only one SQL statement. Several experts have given different methods to check, but all Very complicated, I also tried to use my own method to get the available memory, but it is too complicated for your requirements and is not recommended!
My implementation method：

create table #db_files(  
db_files varchar(300),  
file_loc varchar(300),  
filesizeMB decimal(9,2),  
spaceUsedMB decimal(9,2),  
FreespaceMB decimal(9,2))  
  
declare @strSQL nvarchar(2000)  
DECLARE @dbName varchar(MAX)  
DECLARE @getDBname CURSOR  
  
SET @getDBname = CURSOR FOR  
select name from sys.databases  
  
OPEN @getDBname  
FETCH NEXT  
FROM @getDBname INTO @dbName  
WHILE @@FETCH_STATUS = 0  
BEGIN  
PRINT @dbName  
  
select @strSQL =  
    '  
        use ' + @dbname + '  
        INSERT INTO #db_files  
        select  
      name  
    , filename  
    , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB  
    , convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)) as SpaceUsedMB  
    , convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) as FreeSpaceMB  
    from dbo.sysfiles a  
    '  
    exec sp_executesql @strSQL  
  
FETCH NEXT  
FROM @getDBname INTO @dbName  
  
END  
CLOSE @getDBname  
DEALLOCATE @getDBname  
GO  
  
select * from #db_files  

In summary, what you have shown us so far is the optimal way to view the remaining space！

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Check this link (I haven't re-tested, I wrote that so long ago):

https://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/how-to-get-information-about-all-databas#9

Answer 3

The single query would be:

SELECT DB_NAME() AS DbName, 
         a.name AS FileName, 
         a.type_desc,
         a.size * 8 AS CurrentSizeKb,  
         a.size/128.0 AS CurrentSizeMB,  
         a.size * 8 - CAST(FILEPROPERTY(a.name, 'SpaceUsed') AS INT) * 8 AS FreeSpaceKb,
         a.size/128.0 - CAST(FILEPROPERTY(a.name, 'SpaceUsed') AS INT) / 128.0 AS FreeSpaceMB
     FROM DB1.sys.database_files a
    UNION ALL
     SELECT DB_NAME() AS DbName, 
         a.name AS FileName, 
         a.type_desc,
         a.size * 8 AS CurrentSizeKb,  
         a.size/128.0 AS CurrentSizeMB,  
         a.size * 8 - CAST(FILEPROPERTY(a.name, 'SpaceUsed') AS INT) * 8 AS FreeSpaceKb,
         a.size/128.0 - CAST(FILEPROPERTY(a.name, 'SpaceUsed') AS INT) / 128.0 AS FreeSpaceMB
     FROM DB2.sys.database_files a
     UNION ALL
    -- etc

You may not want to type it, so here is a query to generate it:

DECLARE @sql nvarchar(MAX)
 SELECT @sql = string_agg(convert(nvarchar(MAX), '
         SELECT ' + quotename(name, '''') + ' AS DBName,
         a.name COLLATE Latin1_General_100_CI_AS_KS_WS AS FileName, 
         a.type_desc,
         a.size * 8 AS CurrentSizeKb,  
         a.size/128.0 AS CurrentSizeMB,  
         a.size * 8 - CAST(FILEPROPERTY(a.name, ''SpaceUsed'') AS INT) * 8 AS FreeSpaceKb,
         a.size/128.0 - CAST(FILEPROPERTY(a.name, ''SpaceUsed'') AS INT) / 128.0 AS FreeSpaceMB
     FROM ' + quotename(name) + '.sys.database_files a'), 
     char(13) + char(10) + 'UNION ALL ' + char(13) + char(10))
FROM  sys.databases
PRINT @sql
EXEC(@sql)

Answer 4

Use sys.master_files instead, it returns the files information for all databases in one shot

select *  
from sys.master_files  

See https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-master-files-transact-sql?view=sql-server-ver15