To see all the databases with their size on an instance, you can use sp_helpdb. That works but returns the results in some random order. In my case I see master, model and

msdb followed by a couple of user database, then tempdb and then again some user databases. What if I want to get the list returned order by size descending? This is pretty easy if you dump the results into a table and then do the sorting when doing the SELECT query against this table. The one thing you have to do is taking out MB from the db_size column and converting it to something numeric.

Here is what the query looks like

CREATE TABLE #test (name varchar(100), db_size varchar(100),owner varchar(100),db_id int,created varchar(100),status varchar(1000),compatibility_level int)
 
INSERT #test 
EXEC sp_helpdb
 
SELECT name,db_size,owner,db_id,created,compatibility_level 
FROM #test
ORDER BY CONVERT(float,REPLACE(db_size,' MB','')) DESC

DROP TABLE #test 

And here is what you would see

name		     db_size	owner		db_id	created	    compatibility_level
TestBigger	    988.31 MB	DenisDenis	8	Nov 26 2012	110
TestSmaller	    710.31 MB	DenisDenis	9	Nov 26 2012	110
msdb		     38.00 MB	sa		4	Feb 10 2012	110
master		     18.63 MB	sa		1	Apr  8 2003	110
ReportServer	     12.94 MB	DenisDenis	5	Aug 16 2012	110

This “check” will be part of the informational section of SQLCop