SQL Server query consolidating each database file data in to one row



  • I have this Dataset base of this query below

    SELECT DB_NAME() AS dbname , size/128 AS 'Total Size in MB',
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB', type,name
    FROM sys.database_files;
    

    Dataset

    I want to consolidate the data so that each database will only have 1 row but will have a column that counts how many files (log and data) the database has (pic below) enter image description here

    How do i make this query where tempdb will only have 1 row and will count the number of files for data and log and store it in separate columns? The closest i got is the query below but its segregated base of the file type (log or data). Help would be appreciated

    SELECT DB_NAME() AS dbname, CASE type WHEN 0 THEN 'Data File' WHEN 1 THEN 'Log File' END AS [FileType], COUNT(type) AS [File Count], SUM(size/128) AS 'Total Size in MB',
    SUM(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0) AS 'Available Space In MB'--, *
    FROM sys.database_files
    WHERE type IN(1,0)
    GROUP BY type
    

    enter image description here



  • Try creating a CASE statement for each column type you want to return. I.E. Data file vs Log file detail. Below is an example.

    SELECT DB_NAME(database_id) AS dbname,
        SUM(CASE WHEN type = 0 THEN 1 ELSE 0 END) AS [Data File Count],
        SUM(CASE WHEN type = 1 THEN 1 ELSE 0 END) AS [Log File Count],
        SUM(CASE WHEN type = 0 THEN size/128 ELSE 0 END) AS 'Data File Size in MB',
        SUM(CASE WHEN type = 1 THEN size/128 ELSE 0 END) AS 'Log File Size in MB',
        SUM(CASE WHEN type = 0 THEN size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 ELSE 0 END) AS 'Available Data File Space In MB'
    FROM sys.master_files
    WHERE type IN (1,0)
    GROUP BY DB_NAME(database_id)
    
    dbname Data File Count Log File Count Data File Size in MB Log File Size in MB Available Data File Space In MB
    db_name 1 1 8 8 4.937500



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2