SQL Server Portal

I came across a question in the Forum, how to calculate the growth of all databases in a server.
So, I set out with my research for the best solution.

Given below are the two methods to calculate the growth of all databases in the server:

• Via sp_MSforeachdb
• Via sys.master_files

Given below is the workout that will help you understand the best way to calculate.

• sp_MSforeachdb

Lets execute sp_MSforeachdb and view the statistics time.

SET STATISTICS TIME ON
exec sp_MSforeachdb 'use [?]; EXEC sp_helpfile'
--OUTPUT

After executing the above statement, I saw few server executions of sp_MSforeachdb. Given below is one of the server executions.
SQL Server Execution Times:
CPU time = 343 ms, elapsed time = 10050 ms.

• sys.master_files

Given below is the query that  I copied from MS SQL Server profiler & modified it. (Just view growth from SSMS; you can find this query in the profiler)

SELECT
S.[name] AS [Logical Name]
,S.[file_id] AS [File ID]
, S.[physical_name] AS [File Name]
,CAST(CAST(G.name AS VARBINARY(256)) AS sysname) AS [FileGroup_Name]
,CONVERT (varchar(10),(S.[size]*8)) + ' KB' AS [Size]
,CASE WHEN S.[max_size]=-1 THEN 'Unlimited' ELSE CONVERT(VARCHAR(10),CONVERT(bigint,S.[max_size])*8) +' KB' END AS [Max Size]
,CASE s.is_percent_growth WHEN 1 THEN CONVERT(VARCHAR(10),S.growth) +'%' ELSE Convert(VARCHAR(10),S.growth*8) +' KB' END AS [Growth]
,Case WHEN S.[type]=0 THEN 'Data Only'
WHEN S.[type]=1 THEN 'Log Only'
WHEN S.[type]=2 THEN 'FILESTREAM Only'
WHEN S.[type]=3 THEN 'Informational purposes Only'
WHEN S.[type]=4 THEN 'Full-text '
END AS [usage]
,DB_name(S.database_id) AS [Database Name]
FROM sys.master_files AS S
LEFT JOIN sys.filegroups AS G ON ((S.type = 2 OR S.type = 0)
AND (S.drop_lsn IS NULL)) AND (S.data_space_id=G.data_space_id)
--OUTPUT

After executing the above query, I saw server execution of sys.master_files and I was really impressed.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 3 ms.

Conclusion :
Why there is a vast difference between two methods even though the output is almost the same ? Because sp_MSforeachdb runs sp_help against each database but on the other hand sys.master_files has all the data and you just need to present it.
The second difference between both is, if you need to modify sp_MSforeachdb result set, you need to insert it into temporary table and use it but in sys.master_files result set, you can do whatever you want without using any temporary table.

Let me know if you know other sound method to check auto growth settings of all databases.