Query to extract .mdf and .ldf files from a server

With thanks to Ken Simmons

USE MASTER 

GO 


CREATE TABLE #TMPSPACEUSED ( 
  DBNAME    VARCHAR(50), 
  FILENME   VARCHAR(50), 
  SPACEUSED FLOAT) 

INSERT INTO #TMPSPACEUSED 
EXEC( 'sp_msforeachdb''use ?; Select ''''?'''' DBName, Name FileNme, fileproperty(Name,''''SpaceUsed'''') SpaceUsed from sysfiles''') 

SELECT 
         A.NAME AS DATABASENAME, 
         B.NAME AS FILENAME, 
         CASE B.TYPE  
           WHEN 0 THEN 'DATA' 
           ELSE TYPE_DESC 
         END AS FILETYPE, 
         CASE  
           WHEN (B.SIZE * 8 / 1024.0) > 1000 THEN CAST(CAST(((B.SIZE * 8 / 1024) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB' 
           ELSE CAST(CAST((B.SIZE * 8 / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB' 
         END AS FILESIZE, 
         CAST((B.SIZE * 8 / 1024.0) - (D.SPACEUSED / 128.0) AS DECIMAL(15,2))    SPACEFREE_MB, 
         B.PHYSICAL_NAME 
FROM     SYS.DATABASES A 
         JOIN SYS.MASTER_FILES B 
           ON A.DATABASE_ID = B.DATABASE_ID 
    
         JOIN #TMPSPACEUSED D 
           ON A.NAME = D.DBNAME 
              AND B.NAME = D.FILENME 
-- where b.physical_name = 'RKH_Feb13_Dev_Empty2_1'
ORDER BY physical_name
          

DROP TABLE #TMPSPACEUSED