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