DECLARE @dname VARCHAR(50)  --database name

CREATE TABLE #tmp1
(
[name] VARCHAR(50),
[fileid] INT,
[filename] VARCHAR (150),
[filegroup] VARCHAR(50),
[size] VARCHAR(50),
[maxsize] VARCHAR(50),
[growth] VARCHAR(50),
[useage] VARCHAR (30)
)

DECLARE db_cursor CURSOR FOR 

SELECT [name] FROM sys.databases WHERE [name] NOT IN ('master', 'msdb', 'model')
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @dname   
WHILE @@FETCH_STATUS = 0  
BEGIN   

INSERT INTO #tmp1 

select 	name,  fileid, filename,
	filegroup = filegroup_name(groupid),
	'size' = convert(nvarchar(15), convert (bigint, size) * 8) + N' KB',
	'maxsize' = (case maxsize when -1 then N'Unlimited'
			else
			convert(nvarchar(15), convert (bigint, maxsize) * 8) + N' KB' end),
	'growth' = (case status & 0x100000 when 0x100000 then
		convert(nvarchar(15), growth) + N'%'
		else
		convert(nvarchar(15), convert (bigint, growth) * 8) + N' KB' end),
	'usage' = (case status & 0x40 when 0x40 then 'log only' else 'data only' end)
	from sysfiles 
	order by fileid
FETCH NEXT FROM db_cursor INTO @dname  
END  

CLOSE db_cursor  
DEALLOCATE db_cursor	
	SELECT * FROM #tmp1
	
	
DROP TABLE #tmp1 

quote:

---

select * from sys.master_files

---

CREATE TABLE	#Stage
		(
			dbName NVARCHAR(128),
			fileID INT,
			[fileName] NVARCHAR (128),
			fileGroup NVARCHAR(128),
			size NVARCHAR(128),
			maxSize NVARCHAR(128),
			growth NVARCHAR(128),
			usage NVARCHAR (128)
		)

INSERT		#Stage
		(
			dbName,
			fileID,
			[fileName],
			fileGroup,
			size,
			maxSize,
			growth,
			usage
		)
SELECT		mf.name AS dbName,
		mf.[file_id] AS fileID,
		mf.physical_name AS fileName,
		ds.name AS fileGroup,
		CONVERT(NVARCHAR(128), CONVERT(BIGINT, mf.size) * 8) + N' KB' AS size,
		CASE mf.max_size
			WHEN -1 THEN N'Unlimited'
			ELSE CONVERT(NVARCHAR(128), CONVERT (BIGINT, mf.max_size) * 8) + N' KB'
		END AS maxSize,
		CASE mf.is_percent_growth
			WHEN 1 THEN CONVERT(NVARCHAR(128), mf.growth) + N'%'
			ELSE CONVERT(NVARCHAR(128), CONVERT (BIGINT, mf.growth) * 8) + N' KB'
		END AS growth,
		CASE mf.data_space_id
			WHEN 0 THEN 'log only'
			ELSE 'data only'
		END AS Usage
FROM		master.sys.master_files AS mf
LEFT JOIN	master.sys.data_spaces AS ds ON ds.data_space_id = mf.data_space_id
WHERE		mf.name NOT IN ('master', 'mastlog', 'modeldev', 'modellog', 'msdbdata', 'msdblog', 'tempdev', 'templog', 'distribution', 'distribution_log')

SELECT	*
FROM	#Stage

DROP TABLE	#Stage