SQL Server Performance

table space

Discussion in 'T-SQL Performance Tuning for Developers' started by alex, Sep 27, 2004.

  1. alex New Member

    Hello, 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.

  2. FrankKalis Moderator

    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!)

  3. alex New Member

    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?
  4. thomas New Member

    you could create a list by joining sysobjects to sysindexes and looping through it.


    select object_name(so.id)
    from sysobjects so
    join sysindexes si
    on so.id=si.id
    where si.rows=0
    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
  5. Luis Martin Moderator


    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


    Luis Martin

    All postings are provided “AS IS” with no warranties for accuracy.

Share This Page