There are usually several data marts or a warehouse that is physically split up over many different databases, and it can take a few days to figure this out if there are many data files. Another question I ask is how full are your data marts or warehouse? You would be surprised at how many companies or BI teams struggle with this information or just don’t know that it is important. This info can help with capacity planning and performance as well. If your data files have to grow a few times a week to meet increasing data needs you are taking a performance hit each time the data file has to grow.
To simplify these questions, I wrote a script a few years ago that make the answers easy to attain. Disclaimer: I gathered some of the guts of the #datainfo T-SQL from a SQL Server forum a few years ago. Credit goes to the author of it as it got me thinking to create this script and his / her thought process was very cool.
I hope this script helps those out that struggle with these questions. This script can obviously be used to answer any database space question or modified to suit your needs rather easily.
This script will gather all the database sizes and log file sizes on a SQL server rolled up.
The results look like (click to make larger):