1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
| USE TempDB
GO
;WITH
CTE AS
(
SELECT name AS nomLogique,
physical_name AS nomPhysique,
CASE is_percent_growth
WHEN 0 THEN CAST(growth * 8 / 1024 AS VARCHAR(8)) + ' MB'
ELSE CAST(growth AS VARCHAR(3)) + ' %'
END AS accroissement,
CAST((size * 8) / CAST(1024 AS NUMERIC(14, 2)) AS NUMERIC(14, 2)) AS tailleFichier_MB,
CAST((FILEPROPERTY(name, 'SpaceUsed') * 8) / CAST(1024 AS NUMERIC(14, 2)) AS NUMERIC(14, 2)) AS espaceOccupe
FROM sys.database_files
)
SELECT nomLogique,
nomPhysique,
tailleFichier_MB,
accroissement,
espaceOccupe AS espaceOccupe_MB,
tailleFichier_MB - espaceOccupe AS espaceLibre_MB,
CAST((CAST(espaceOccupe AS NUMERIC(14, 2)) / tailleFichier_MB) * 100 AS NUMERIC(14, 2)) AS [%occupe],
CAST((CAST(tailleFichier_MB - espaceOccupe AS NUMERIC(14, 2)) / tailleFichier_MB) * 100 AS NUMERIC(14, 2)) AS [%libre]
FROM CTE |
Partager