List the largest databases on a SQL Server instance

LargestDBs

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
ORDER BY SizeMB DESC

@Nighteg you might be using a targeted query at database level, this query needs to run at the top server level to hit all dbs.

@agrimm thanks for your suggestion, it solved my overflow error when running

for future finders, I took it even one more step since I have very big DB to determine what they are in GB:

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, ((size/(1024/8))/1000) SizeGB
FROM sys.master_files
ORDER BY SizeGB DESC

Although, I think it also might be important to note, this shows what size each DB is allocated, but, I'm not sure it shows accurately the space each is consuming from that allocated range. For that, see this article: https://www.sqlshack.com/how-to-determine-free-space-and-file-size-for-sql-server-databases/

I didn't run that sqlshack article's query on prod and I was getting syntax errors in test environment so I did not run it at all. I know all the space is free in my case, as the table is empty. In your case, you might want to consider it.

Doesn't work. Whatever's inside your 'database_id', it says "Invalid column name". Also it's unclear what Name is?

Completing the table name with database name "master" in the FROM clause avoids this
This code runs independently of the selected database

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, ((size/(1024/8))/1000) SizeGB
FROM master.sys.master_files
ORDER BY SizeGB DESC