Here's the one I use day-to-day, it avoids cursors.
/* SQL 2005+ VERSION */
SELECT type_desc'Type',SUM(size)/128'TotalServerSizeMB'
FROM sys.master_files
GROUP BY type_desc
ORDER BY TotalServerSizeMB DESC
SELECT
database_id'dbID',
DB_NAME(database_id)'dbName',
file_id,
name'LogicalName',
type_desc'Type',
Physical_Name,
state_desc'Status',
differential_base_time'LastFullBackup',
CAST(size/128.0+0.5 as int)'Size(MB)',
CAST(max_size/128.0+0.5 as int)'MaxSize(MB)',
CASE is_percent_growth
WHEN 0 THEN CAST(growth/128 as varchar(10))+' MB'
ELSE CAST(growth as varchar(10))+' %'
END AS 'AutoGrowth',
'USE ['+DB_NAME(database_id)+'];
DBCC SHRINKFILE('+CAST(file_id as varchar(2))+',1,TRUNCATEONLY);' AS 'ShrinkCommand',
'ALTER DATABASE ['+DB_NAME(database_id)+'] MODIFY FILE (NAME = ['+[name]+'] , FILEGROWTH = '+
CASE
WHEN size>=128*1000 THEN'100MB' --size >= 1000MB
WHEN size>=128* 500 THEN '50MB' --size >= 500MB
WHEN size>=128* 100 THEN '10MB' --size >= 100MB
WHEN size*growth<=12800 OR size<=128*10 THEN '1MB' --growth < 1MB or size < 10MB
ELSE '5MB' --sizes < the above and > 10MB
END+
')'AS'FixAutoGrowthCommand'
FROM master.sys.master_files
WHERE 1=1
--AND database_id>4 --user DBs only
--AND database_id<=4 --system DBs only
--AND type_desc = 'ROWS' --data only
--AND type_desc = 'LOG' --transaction logs only
ORDER BY --size DESC,
DB_NAME(database_id),
file_id
/* SQL 2000 VERSION
SELECT CASE groupid WHEN 1 THEN'ROWS'ELSE'LOGS'END'Type',SUM(size)/128'TotalServerSizeMB'
FROM master..sysaltfiles
GROUP BY groupid
ORDER BY TotalServerSizeMB DESC
SELECT
dbID,
DB_NAME(dbid)'DB',
fileid,
name,
CASE groupid WHEN 1 THEN'ROWS'ELSE'LOGS'END'Type',
filename,
CAST(size/128.0+0.5 as int)'Size(MB)',
CAST(maxsize/128.0+0.5 as int)'MaxSize(MB)',
CASE
WHEN growth>=128 THEN CAST(growth/128 as varchar(10))+' MB'
ELSE CAST(growth as varchar(10))+' %'
END AS 'AutoGrowth',
'USE ['+DB_NAME(dbid)+'];DBCC SHRINKFILE('+CAST(fileid as varchar(2))+',1,TRUNCATEONLY);' AS 'ShrinkCommand'
FROM master..sysaltfiles
WHERE 1=1
--AND DBid > 4 --user DBs only
--AND DBid <=4 --system DBs only
--AND groupid = 1 --data only
--AND groupid = 0 --transaction logs only
ORDER BY size DESC,
DB_NAME(dbid),
fileid
*/