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.

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

    -----------------------
    --Frank
    http://www.insidesql.de
    -----------------------
  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.

    e.g.

    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

    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
    Moderator
    SQL-Server-Performance.com

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

Share This Page