Blog > Juin 2015 > Lister les fichiers des bases de données d'une instance
Lister les fichiers des bases de données d'une instance


Voici un script permettant de lister les fichiers de base de données sur le disque. Ce script peut être pratique si vous avez un peu de bazarre sur le serveur.
SELECT	[name]	AS [Logical Name],		[physical_name]	AS [Physical Name], 
		0		AS [Size (MB)],			0				AS [Free (MB)], 
		0		AS [Percent Used],		[type_desc]		AS [Growth by],
		[type]	AS [Type] 
INTO #DATABASEFILE_SIZE
FROM master.sys.database_files WHERE 1 = 0; -- Seule la structure nous intéresse.

EXEC sp_MSforeachdb '
USE [?]
INSERT INTO #DATABASEFILE_SIZE
SELECT name, physical_name, (size/128),
(size - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS int))/128,
ROUND((CAST(FILEPROPERTY(name, ''SpaceUsed'') AS float)/size*100), 0), 
CASE 
	WHEN is_percent_growth = 1 THEN (CAST(growth AS VARCHAR(20)) + ''%'')
	WHEN growth = 0 THEN ''None'' 
	ELSE CAST(growth/128 AS VARCHAR(20)) END,
[type]
FROM sys.database_files
WHERE [type] <> 1 -- Ignorer les fichiers de log
;'

SELECT * FROM #DATABASEFILE_SIZE ORDER BY [Physical Name] ASC;
DROP TABLE #DATABASEFILE_SIZE;