Database Sizes

The script below is very useful for listing database sizes. However sometimes it comes up with the following error…

Cannot insert the value NULL into column ” owner”;

This is the result of a database not having an owner which is quite a serious condition. Happily it is easiy identified like this …

select name, suser_sname(sid) owner from master..sysdatabases

… and fixed like this …

alter authorization on database::[SomeDatabase] to sa;

Or this (which is depreceiated from SQL2012) …

exec sp_changedbowner ‘sa’;

Back on topic 🙂 … here’s the script …

— dbsize.sql
set nocount on

create table #sizes (
name varchar(50),
db_size varchar(50),
owner varchar(50),
dbid char(1),
created smalldatetime,
status varchar(500),
compatability_level char(2))

insert #sizes exec sp_helpdb

select name, db_size
from #sizes
order by db_size desc

drop table #sizes