Space allocated to the databases by disk letters

  • Comments posted to this topic are about the item Space allocated to the databases by disk letters

  • CREATE PROCEDURE spDbFileAllocation
    @db VARCHAR(255) = ''
    AS BEGIN
    DECLARE @qry VARCHAR(MAX) = '';

    SET @qry = CONCAT(
    ';WITH tbl AS (
    SELECT *
    FROM (SELECT [dbName] = ''',@db,''',
    [DiskDrive] = SUBSTRING(physical_name, 1, 3),
    [GB] = (8.0*max(size))/1024/1024,
    name
    FROM sys.master_files GROUP BY SUBSTRING(physical_name, 1, 3), name) x
    PIVOT (
    max(GB)
    FOR name IN ([',@db,'], [',@db,'_log])
    ) p
    ) SELECT [dbName] = dbName,
    [Drive] = DiskDrive,
    [Database] = ',@db,',
    [LogFile] = ',@db,'_log,
    [TotalSize] = ',@db,' + ',@db,'_log
    FROM tbl');
    EXEC(@qry)
    END

    Great script!  Thanks.  Used it to create a generic procedure.  Pivoted the data as well.  I'm sure this could be improved on but it is doing the job for me.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply