-- Some queries to retreive information about files, filegroups, paths, sizes etc..
-- usable for SQL 2005/2008
 
 
-- Go to the database you are interrested in:
-- USE DATABASENAME  -- e.g.: use sales
-- GO
 
 
-- Get all file information:
-- -------------------------
 
SELECT * FROM sys.sysfiles
 
SELECT * FROM sys.database_files
 
 
-- Nice overall listing, and names and free/used space:
-- ----------------------------------------------------
 
SELECT name AS [File Name] , physical_name AS [Physical Name], size/128 AS [Total Size in MB],
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space In MB]
FROM sys.database_files;
 
 
-- Selected info from sysfiles:
-- ----------------------------
 
SELECT sysfiles.fileid, sysfiles.groupid, sysfiles.size,
       (sysfiles.size * 8 / 1024)                AS "SIZE_IN_MB",
       substring(sysfiles.name, 1, 30)           AS NAME, 
       substring(sysfiles.filename, 1, 100)      AS FILENAME, 
       substring(sysfilegroups.groupname, 1, 40) AS GROUPNAME
FROM   sysfiles, sysfilegroups
WHERE  sysfiles.groupid=sysfilegroups.groupid
 
 
-- Nice listing of dbid's and files:
-- ---------------------------------
 
SELECT dbid, fileid, filename
FROM sys.sysaltfiles order by dbid
 
 
-- Loop to get selected file info of all databases on this Server:
-- ---------------------------------------------------------------
 
DECLARE @dbname   VARCHAR(100)
 
DECLARE cur1 CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
 
OPEN cur1
FETCH NEXT FROM cur1 INTO @dbname
 
WHILE (@@fetch_status<>-1)
BEGIN
PRINT 'DATABASE FILES FOR: ' +@dbname
PRINT '  '
EXEC ('SELECT 
fileid, 
(size * 8 /1024)           AS SIZE_IN_MB,
substring(name, 1, 80)     AS NAME,
substring(filename, 1, 120) AS FILENAME 
FROM '+@dbname+'.dbo.sysfiles')
 
FETCH NEXT FROM cur1 INTO @dbname
END
 
CLOSE cur1
DEALLOCATE cur1