SQL Server Performance Forum – Threads Archive
table spaceHello, friends. I have a bunch of empty tables in my db. How do I see the amount of space they are taking. I am trying to figur out if it make sense to drop them. Thanks.
One way would be
EXEC sp_MSforeachtable ‘sp_spaceused ”?”’
Though it’s undocumented, it’s quite okay to be used for admin purposes, imho.
I wouldn’t drop them right away, but rather rename them first and see if anything breaks. And then after a while drop them (after you have at least a known good backup, of course!) ———————–
Thanks, Frank for your response. Your command is very helpful but not exactly what I need.
I would like to know is how to see only a set of empty tables with this command?
you could create a list by joining sysobjects to sysindexes and looping through it. e.g. select object_name(so.id)
from sysobjects so
join sysindexes si
and so.indid<2 either keep these in a temp table and loop through them, running sp_spaceused on each, or use a cursor (frowned upon, but fine for things like this and obviates the need for a temp table). good idea to update the usage first or run instead dbcc spaceused (params), @updateusage=true. p.s. don’t worry about selecting from system tables. Everyone does it, and it doesn’t hurt. We’re meant to use the INFORMATION_SCHEMA views but they’re cumbersome! Tom Pullen
DBA, Oxfam GB
Try: select si.rows as ‘rows’, SO.Name as Tabla, SFG.groupname as ‘Filegroup’
from sysobjects as SO
join sysindexes as SI
on SO.Id = SI.id
join sysfilegroups as SFG
on SI.GroupId = SFG.GroupId
where si.rows = 0 and si.indid = 1
order by SO.Name , SFG.GroupName
HTH Luis Martin
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.