sys.database_files JOIN for get all database

Alen Cappelletti 951 Reputation points
2022-03-11T17:44:45.76+00:00

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
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,561 questions
0 comments No comments
{count} votes

Accepted answer
  1. Bert Zhou-msft 3,421 Reputation points
    2022-03-14T02:43:41.667+00:00

    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.


3 additional answers

Sort by: Most helpful
  1. Naomi 7,361 Reputation points
    2022-03-11T17:53:25.087+00:00

  2. Erland Sommarskog 102.2K Reputation points
    2022-03-11T22:10:24.807+00:00

    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)
    

  3. Olaf Helper 41,011 Reputation points
    2022-03-12T08:52:47.007+00:00

    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