27

I need a T-SQL query for a list of all databases in SQL Server 2008 showing

  • the name of the database and
  • the location of the .mdf and .ldf files.
Heinzi
  • 167,459
  • 57
  • 363
  • 519
Mehdi Haghshenas
  • 2,433
  • 1
  • 16
  • 35
  • Please use the table, sys.database_files – Jithin Shaji Nov 21 '14 at 08:32
  • @MehdiHaghshenas If you spend 2 minutes on Google, `mssql get all databases and locations` returned 26 million results. – Jaques Nov 21 '14 at 08:49
  • 2
    ironically, this question is now the third result when googling the exact search phrase suggested above – rdans Apr 18 '16 at 11:26
  • 7
    Voting to reopen. It does not matter how much effort the OP put in it; it's now a high-ranking result on Google and apparently helps a lot of people. – Heinzi Apr 25 '16 at 14:13

3 Answers3

56
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

enter image description here

lava
  • 6,020
  • 2
  • 31
  • 28
Raj
  • 10,653
  • 2
  • 45
  • 52
8

enter image description here

select 
    d.name as 'database',
    mdf.physical_name as 'mdf_file',
    ldf.physical_name as 'log_file'
from sys.databases d
inner join sys.master_files mdf on 
    d.database_id = mdf.database_id and mdf.[type] = 0
inner join sys.master_files ldf on 
    d.database_id = ldf.database_id and ldf.[type] = 1
lava
  • 6,020
  • 2
  • 31
  • 28
Alberto Spelta
  • 3,578
  • 2
  • 21
  • 20
-4

Try this query: 

SELECT name [Database Name], physical_name [File Location],size *(8.0 / 1024) /1024 [Size in GBs]
FROM sys.master_files
iamsankalp89
  • 4,607
  • 2
  • 15
  • 36
Nadeem
  • 1
  • While this answers the question, this is basically a repeat of other answers. In this case the proper way to go about this is upvote other answers (once you have sufficient reputation), not repeat another answer. – TT. Sep 14 '17 at 10:12