Listing information about all database files in SQL Server

Question

Is it possible to list information about the files  MDF LDF  of all databases on an SQL Server    I d like to get a list showing which database is using what files on the local disk   What I tried    exec sp databases all databases select   from sys databases shows a lot of information about each database - but unfortunately it doesn t show the files used by each database  select   from sys database files shows the mdf ldf files of the master database - but not the other databases

User · Answer

If you rename your Database  MS SQL Server does not rename the underlying files    Following query gives you the current name of the database and the Logical file name  which might be the original name of the Database when it was created  and also corresponding physical file names    Note  Un-comment the last line to see only the actual data files  select  db database id           db name  Database Name            files name  Logical File Name           files physical name from    sys master files files          join sys databases db on db database id   files database id  --                           and files type desc    ROWS    Reference    https   docs microsoft com en-us sql relational-databases system-catalog-views sys-master-files-transact-sql view sql-server-ver15  https   docs microsoft com en-us sql relational-databases system-catalog-views sys-databases-transact-sql view sql-server-ver15

User · Answer

You can also try this       select db name dbid  dbname  filename from sys sysaltfiles

User · Answer

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

User · Answer

I ve created this query   SELECT      db name AS                                    Database Name        mf name AS                                    Logical Name        mf type desc AS                               File Type        mf physical name AS                           Path        CAST           mf Size   8             1024 0 AS DECIMAL 18  1   AS          Initial Size  MB          By   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      MB   AS  Autogrowth        IIF mf max size   0   No growth is allowed   IIF mf max size   -1   Unlimited   CAST                            CAST mf max size AS BIGINT    8             1024 AS VARCHAR 30     MB    AS       MaximumSize  FROM       sys master files AS mf      INNER JOIN sys databases AS db ON             db database id   mf database id

User · Answer

If you want get location of Database you can check Get All DBs Location  you can use sys master files for get location of db and sys databse to get db name  SELECT     db name AS DBName      type desc AS FileType      Physical Name AS Location FROM     sys master files mf INNER JOIN      sys databases db ON db database id   mf database id

User · Answer

This script lists most of what you are looking for and can hopefully be modified to you needs  Note that it is creating a permanent table in there - you might want to change it  It is a subset from a larger script that also summarises backup and job information on various servers   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  gt  4 --Exclude basic system databases UPDATE  dbo   Tmp tblDatabaseInfo      SET DataFileLocations          CASE WHEN LEN DataFileLocations   gt  4 THEN  LEFT DataFileLocations LEN DataFileLocations -2  ELSE NULL END     LogFileLocations         CASE WHEN LEN LogFileLocations   gt  4 THEN  LEFT LogFileLocations LEN LogFileLocations -2  ELSE NULL END     DataFilesMB          CASE WHEN DataFilesMB  gt  0 THEN  DataFilesMB   8   1024 0   ELSE NULL END     LogFilesMB          CASE WHEN LogFilesMB  gt  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

User · Answer

The query will error if multiple data files  e g    ndf  file types  are used in one of the databases    Here s a version of your query using joins instead of the sub-queries    Cheers   SELECT     db name AS DBName      db database id      mfr physical name AS DataFile      mfl physical name AS LogFile FROM sys databases db     JOIN sys master files mfr ON db database id mfr database id AND mfr type desc  ROWS      JOIN sys master files mfl ON db database id mfl database id AND mfl type desc  LOG  ORDER BY db database id

User · Answer

I am using script to get empty space in each file   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   Size is expressed in KB

User · Answer

You can use sys master files      Contains a row per file of a database as stored in the master   database  This is a single  system-wide view

User · Answer

You can use the below   SP HELPDB  Master  GO

User · Answer

Using this script you can show all the databases name and files used  with exception of system dbs    select name physical name from sys master files where database id  gt  4

User · Answer

Executing following sql  It will only work when you don t have multiple mdf ldf files for same database   SELECT     db name AS DBName       select mf Physical Name FROM sys master files mf where mf type desc    ROWS  and db database id   mf database id   as DataFile       select mf Physical Name FROM sys master files mf where mf type desc    LOG  and db database id   mf database id   as LogFile FROM sys databases db   will return this output  DBName       DataFile                     LogFile -------------------------------------------------------------------------------- master       C       master mdf           C       mastlog ldf tempdb       C       tempdb mdf           C       templog ldf model        C       model mdf            C       modellog ldf   and rest of the databases  If your TempDB s have multiple MDF s  like mine have   this script will fail  However  you can use   WHERE db database id  gt  4   at the end and it will return all databases except system databases

User · Answer

Below script can be used to get following information   1  DB Size Info  2  FileSpaceInfo  3  AutoGrowth  4  Recovery Model  5  Log reuse backup information  CREATE TABLE  tempFileInformation   DBNAME          NVARCHAR 256    FILENAME       NVARCHAR 256    TYPE           NVARCHAR 120   FILEGROUPNAME   NVARCHAR 120   FILE LOCATION   NVARCHAR 500   FILESIZE MB     DECIMAL 10 2   USEDSPACE MB    DECIMAL 10 2   FREESPACE MB    DECIMAL 10 2   AUTOGROW STATUS NVARCHAR 100    GO  DECLARE  SQL VARCHAR 2000   SELECT  SQL      USE                 INSERT INTO  tempFileInformation             SELECT                   DBNAME           DB NAME                          FILENAME        A NAME                   TYPE             A TYPE DESC                  FILEGROUPNAME     fg name                  FILE LOCATION    a PHYSICAL NAME                  FILESIZE MB       CONVERT DECIMAL 10 2  A SIZE 128 0                   USEDSPACE MB      CONVERT DECIMAL 10 2   A SIZE 128 0 -   A SIZE - CAST FILEPROPERTY A NAME   SPACEUSED    AS INT   128 0                     FREESPACE MB      CONVERT DECIMAL 10 2   A SIZE 128 0 -  CAST FILEPROPERTY A NAME   SPACEUSED    AS INT  128 0                    AUTOGROW STATUS     BY     CASE is percent growth when 0 then cast  growth 128 as varchar 10       MB -                                                                      when 1 then cast  growth as varchar 10         -    ELSE      END                                                                     CASE MAX SIZE WHEN 0 THEN    DISABLED                                                                                       WHEN -1 THEN    UNRESTRICTED                                                                                     ELSE    RESTRICTED TO      CAST MAX SIZE  128 1024  AS VARCHAR 10        GB    END                                                                   CASE IS PERCENT GROWTH WHEn 1 then     autogrowth by percent    else      end     from sys database files A     left join sys filegroups fg on a data space id   fg data space id     order by A type desc A name                  --print  sql      EXEC sp MSforeachdb  SQL     go      SELECT dbSize   fg   d log reuse wait desc d recovery model desc     FROM  tempFileInformation fg     LEFT JOIN sys databases d on fg DBNAME   d name     CROSS APPLY               select dbname                  sum FILESIZE MB  as  totalDBSize MB                   sum FREESPACE MB  as  DB Free Space Size MB                   sum USEDSPACE MB  as  DB Used Space Size MB              from  tempFileInformation             where  dbname   fg dbname             group by dbname      dbSize   go DROP TABLE  tempFileInformation

[sql-server] Listing information about all database files in SQL Server

Examples related to sql-server

Examples related to sql-server-2008

Examples related to tsql

Examples related to database-management