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

Vous pouvez utiliser sys.master_files.

Contient une ligne par fichier d'une base de données telle que stockée dans la base de données principale. Il s'agit d'une vue unique à l'échelle du système.

Answer 2

Si vous voulez connaître l'emplacement de la base de données, vous pouvez consulter Obtenir l'emplacement de toutes les bases de données.
vous pouvez utiliser sys.master_files pour obtenir l'emplacement de la base de données et sys.database pour obtenir le nom de la base de données

SELECT
    db.name AS NomDB,
    type_desc AS TypeFichier,
    Physical_Name AS Emplacement
FROM
    sys.master_files mf
INNER JOIN 
    sys.databases db ON db.database_id = mf.database_id

Answer 3

Je suis en train d'utiliser un script pour obtenir de l'espace libre dans chaque fichier :

Create Table ##temp
(
    DatabaseName sysname,
    Name sysname,
    physical_name nvarchar(500),
    size decimal (18,2),
    FreeSpace decimal (18,2)
)   
Exec sp_msforeachdb '
Use [?];
Insert Into ##temp (DatabaseName, Name, physical_name, Size, FreeSpace)
    Select DB_NAME() AS [DatabaseName], Name,  physical_name,
    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) as nvarchar) Size,
    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) -
        Cast(FILEPROPERTY(name, ''SpaceUsed'') * 8.0/1024.0 as decimal(18,2)) as nvarchar) As FreeSpace
    From sys.database_files
'
Select * From ##temp
drop table ##temp

La taille est exprimée en Ko.

Answer 4

J'ai créé cette requête :

SELECT 
    db.name AS                                   [Nom de la base de données], 
    mf.name AS                                   [Nom logique], 
    mf.type_desc AS                              [Type de fichier], 
    mf.physical_name AS                          [Chemin], 
    CAST(
        (mf.Size * 8
        ) / 1024.0 AS DECIMAL(18, 1)) AS         [Taille initiale (Mo)], 
    'Par '+IIF(
            mf.is_percent_growth = 1, CAST(mf.growth AS VARCHAR(10))+'%', CONVERT(VARCHAR(30), CAST(
        (mf.growth * 8
        ) / 1024.0 AS DECIMAL(18, 1)))+' Mo') AS [Croissance automatique], 
    IIF(mf.max_size = 0, 'Aucune croissance autorisée', IIF(mf.max_size = -1, 'Illimité', CAST(
        (
                CAST(mf.max_size AS BIGINT) * 8
        ) / 1024 AS VARCHAR(30))+' Mo')) AS      [Taille maximale]
FROM 
     sys.master_files AS mf
     INNER JOIN sys.databases AS db ON
            db.database_id = mf.database_id

Answer 5

Exécution de la requête SQL suivante (ne fonctionnera que si vous n'avez pas plusieurs fichiers mdf/ldf pour la même base de données)

SELECT
    db.name AS NomBD,
    (select mf.Physical_Name FROM sys.master_files mf where mf.type_desc = 'ROWS' and db.database_id = mf.database_id ) as FichierDonnées,
    (select mf.Physical_Name FROM sys.master_files mf where mf.type_desc = 'LOG' and db.database_id = mf.database_id ) as FichierJournal
FROM sys.databases db

renverra cette sortie

NomBD       FichierDonnées                     FichierJournal
--------------------------------------------------------------------------------
master       C:\....\master.mdf           C:\....\mastlog.ldf
tempdb       C:\....\tempdb.mdf           C:\....\templog.ldf
model        C:\....\model.mdf            C:\....\modellog.ldf

et le reste des bases de données

Si vos TempDB ont plusieurs fichiers MDF (comme les miens), ce script échouera. Cependant, vous pouvez utiliser

WHERE db.database_id > 4

à la fin et il renverra toutes les bases de données sauf les bases de données système.