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 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47
| DECLARE @t_spaceused TABLE
(
nomBase SYSNAME,
nomLogique VARCHAR(100),
nomPhysique VARCHAR(100),
tailleFichier_MB DECIMAL(14,2),
accroissement VARCHAR(15),
espaceOccupe_MB DECIMAL(8,2),
espaceLibre_MB DECIMAL(14,2),
[%libre] DECIMAL(5,2)
);
DECLARE @sql VARCHAR(1000);
SET @sql = 'USE [' + '?' + '];
WITH fichiers_bases AS
(
SELECT
DB_NAME() AS nomBase,
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(8)) + '' %''
END AS accroissement,
(size * 8.0) / 1024 AS tailleFichier_MB,
(FILEPROPERTY(name, ''SpaceUsed'') * 8.0) / 1024 AS espaceOccupe
FROM sys.database_files
--WHERE type = 1 -- Données et journaux
)
SELECT
nomBase,
nomLogique,
nomPhysique,
tailleFichier_MB,
accroissement,
espaceOccupe AS espaceOccupe_MB,
CAST(tailleFichier_MB - espaceOccupe AS DECIMAL(14,2)) AS espaceLibre_MB,
CAST((tailleFichier_MB - espaceOccupe) / tailleFichier_MB * 100.0 AS DECIMAL(5,2)) AS [%libre]
FROM fichiers_bases;';
INSERT INTO @t_spaceused
EXEC sp_MSForEachDB @sql;
SELECT *
FROM @t_spaceused
WHERE nomBase NOT IN ('master','msdb','model','ReportServer','ReportServerTempDB') -- Bases utilisateurs + tempdb
ORDER BY [nomBase] ASC; |
Partager