Affichage des informations sur tous les fichiers de base de données dans SQL Server

Question

Est-il possible de répertorier les informations sur les fichiers (MDF/LDF) de toutes les bases de données sur un serveur SQL ?

J'aimerais obtenir une liste montrant quelles bases de données utilisent quels fichiers sur le disque local.

Ce que j'ai essayé :

• exec sp_databases toutes les bases de données
• select * from sys.databases montre beaucoup d'informations sur chaque base de données - mais malheureusement cela ne montre pas les fichiers utilisés par chaque base de données.
• select * from sys.database_files montre les fichiers mdf/ldf de la base de données master - mais pas des autres bases de données

Answer 1

Ce script répertorie la plupart de ce que vous recherchez et peut éventuellement être modifié selon vos besoins. Notez qu'il crée une table permanente - vous voudrez peut-être le modifier. C'est un sous-ensemble d'un script plus volumineux qui résume également les informations de sauvegarde et de tâches sur divers serveurs.

IF OBJECT_ID('tempdb..#DriveInfo') IS NOT NULL
 DROP TABLE #DriveInfo
CREATE TABLE #DriveInfo
 (
    Drive CHAR(1)
    ,MBFree INT
 ) 

INSERT INTO #DriveInfo
      EXEC master..xp_fixeddrives

IF OBJECT_ID('[dbo].[Tmp_tblDatabaseInfo]', 'U') IS NOT NULL 
   DROP TABLE [dbo].[Tmp_tblDatabaseInfo]
CREATE TABLE [dbo].[Tmp_tblDatabaseInfo](
      [ServerName] [nvarchar](128) NULL
      ,[DBName] [nvarchar](128)  NULL
      ,[database_id] [int] NULL
      ,[create_date] datetime NULL
      ,[CompatibilityLevel] [int] NULL
      ,[collation_name] [nvarchar](128) NULL
      ,[state_desc] [nvarchar](60) NULL
      ,[recovery_model_desc] [nvarchar](60) NULL
      ,[DataFileLocations] [nvarchar](4000)
      ,[DataFilesMB] money null
      ,DataVolumeFreeSpaceMB INT NULL
      ,[LogFileLocations] [nvarchar](4000)
      ,[LogFilesMB] money null
      ,LogVolumeFreeSpaceMB INT NULL
) ON [PRIMARY]

INSERT INTO [dbo].[Tmp_tblDatabaseInfo] 
SELECT 
      @@SERVERNAME AS [ServerName] 
      ,d.name AS DBName 
      ,d.database_id
      ,d.create_date
      ,d.compatibility_level  
      ,CAST(d.collation_name AS [nvarchar](128)) AS collation_name
      ,d.[state_desc]
      ,d.recovery_model_desc
      ,(select physical_name + ' | ' AS [text()]
         from sys.master_files m
         WHERE m.type = 0 and m.database_id = d.database_id
         ORDER BY file_id
         FOR XML PATH ('')) AS DataFileLocations
      ,(select sum(size) from sys.master_files m WHERE m.type = 0 and m.database_id = d.database_id)  AS DataFilesMB
      ,NULL
      ,(select physical_name + ' | ' AS [text()]
         from sys.master_files m
         WHERE m.type = 1 and m.database_id = d.database_id
         ORDER BY file_id
         FOR XML PATH ('')) AS LogFileLocations
      ,(select sum(size) from sys.master_files m WHERE m.type = 1 and m.database_id = d.database_id)  AS LogFilesMB
      ,NULL
FROM  sys.databases d  

WHERE d.database_id > 4 --Exclude basic system databases
UPDATE [dbo].[Tmp_tblDatabaseInfo] 
   SET DataFileLocations = 
      CASE WHEN LEN(DataFileLocations) > 4 THEN  LEFT(DataFileLocations,LEN(DataFileLocations)-2) ELSE NULL END
   ,LogFileLocations =
      CASE WHEN LEN(LogFileLocations) > 4 THEN  LEFT(LogFileLocations,LEN(LogFileLocations)-2) ELSE NULL END
   ,DataFilesMB = 
      CASE WHEN DataFilesMB > 0 THEN  DataFilesMB * 8 / 1024.0   ELSE NULL END
   ,LogFilesMB = 
      CASE WHEN LogFilesMB > 0 THEN  LogFilesMB * 8 / 1024.0  ELSE NULL END
   ,DataVolumeFreeSpaceMB = 
      (SELECT MBFree FROM #DriveInfo WHERE Drive = LEFT( DataFileLocations,1))
   ,LogVolumeFreeSpaceMB = 
      (SELECT MBFree FROM #DriveInfo WHERE Drive = LEFT( LogFileLocations,1))

select * from [dbo].[Tmp_tblDatabaseInfo]

Answer 2

Si vous renommez votre base de données, MS SQL Server ne renomme pas les fichiers sous-jacents.

La requête suivante vous donne le nom actuel de la base de données et le nom du fichier logique (qui pourrait être le nom original de la base de données lors de sa création), ainsi que les noms des fichiers physiques correspondants.

Note : Décommentez la dernière ligne pour voir seulement les fichiers de données réels

select  db.database_id, 
        db.name "Nom de la base de données", 
        files.name "Nom du fichier logique",
        files.physical_name
from    sys.master_files files 
        join sys.databases db on db.database_id = files.database_id 
--                           and files.type_desc = 'ROWS'

Référence:

https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-master-files-transact-sql?view=sql-server-ver15

https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-databases-transact-sql?view=sql-server-ver15

Answer 3

Utiliser la procédure stockée sp_MSForEachDB est une option

EXEC sp_MSForEachDB 'use ? select * from sys.database_files'

De plus, pour voir seulement le nom complet du chemin et les informations sur la taille

EXEC sp_MSForEachDB '
USE [?];
SELECT DB_NAME() AS NomDb, 
    physical_name AS CheminComplet,
    name AS NomFichier, 
    type_desc,
    size/128.0 AS TailleActuelleMo,  
    size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0 AS EspaceLibreMo
FROM sys.database_files
WHERE type IN (0,1);
'

Answer 4

Just adding my 2 cents .

if specifically looking to find total free space only in Data files or only in Log files in all the databases, we can use "data_space_id" column. 1 is for data files and 0 for log files.

CODE:

Create Table ##temp
(
    DatabaseName sysname,
    Name sysname,
    spacetype sysname,
    physical_name nvarchar(500),
    size decimal (18,2),
    FreeSpace decimal (18,2)
)   
Exec sp_msforeachdb '
Use [?];

Insert Into ##temp (DatabaseName, Name,spacetype, physical_name, Size, FreeSpace)
    Select DB_NAME() AS [DatabaseName], Name,   ***data_space_id*** , physical_name,
    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2))/1024 as nvarchar) SizeGB,
    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2)/1024 as decimal(18,2)) -
        Cast(FILEPROPERTY(name, ''SpaceUsed'') * 8.0/1024.0 as decimal(18,2))/1024 as nvarchar) As FreeSpaceGB
    From sys.database_files'

select  
    databasename
    , sum(##temp.FreeSpace) 
from 
    ##temp 
where 
    ##temp.spacetype = 1  
group by 
    DatabaseName

drop table ##temp

Answer 5

Vous pouvez utiliser ce qui suit:

SP_HELPDB [Master]
GO