Great tip! The more I dig into those sys tables the more handy hints I find :)
I assume you are using SQL Server?
My favourite of the moment is this script that shows schema name, table name, row counts, total space and unused space
s.name as schemaname,
t.name as tablename,
p.rows as rowcounts,
sum(a.total_pages) * 8 as totalspacekb,
cast(round(((sum(a.total_pages) * 8) / 1024.00), 2) as numeric(36, 2)) as totalspacemb,
sum(a.used_pages) * 8 as usedspacekb,
cast(round(((sum(a.used_pages) * 8) / 1024.00), 2) as numeric(36, 2)) as usedspacemb,
(sum(a.total_pages) - sum(a.used_pages)) * 8 as unusedspacekb,
cast(round(((sum(a.total_pages) - sum(a.used_pages)) * 8) / 1024.00, 2) as numeric(36, 2)) as unusedspacemb
sys.indexes i on t.object_id = i.object_id
sys.partitions p on i.object_id = p.object_id and i.index_id = p.index_id
sys.allocation_units a on p.partition_id = a.container_id
left outer join
sys.schemas s on t.schema_id = s.schema_id
t.name not like 'dt%'
and t.is_ms_shipped = 0
and i.object_id > 255
t.name, s.name, p.Rows
I am using SQL Server, yep!
This was my first dive into sys tables, it's nothing that was discussed in my studies, so I appreciate your script as well. I'm going to keep that one in mind because I'm sure it will come in handy in the future. Thanks for sharing!
We're a place where coders share, stay up-to-date and grow their careers.
We strive for transparency and don't collect excess data.