How to determine (via transact SQL) space usage per table

Posted on 2004-12-01
Last Modified: 2008-01-09
I know I can use Enterprise Manager to display rowcounts and space usage on a per table basis
(Select database, Views, TaskPad and then look at the table info tab). I would like to be able to get the same information via transact SQL so that I can manipulate the results programmatically.

Question by:gwalkeriq
    LVL 5

    Assisted Solution

    Here is half of your solution:

    exec sp_spaceused mytable

    This will return the data you want, but only to the console. To get it into variables, I think you may have to copy sp_spaceused and hack it to have output parameters.
    LVL 7

    Assisted Solution

    CREATE TABLE #UsageData
         [name] varchar(255)
         , [rows] varchar(255)
         , [reserved] varchar(255)
         , [data] varchar(255)
         , index_size varchar(255)
         , [unused] varchar(255)

    INSERT #UsageData
    exec sp_spaceused mytable

    This gives you a temp table with data you could use elsewhere
    LVL 18

    Accepted Solution

    These are the guts of a stored proc I use to see which tables are hoggin' the most. Let me know if this helps.

      Set NoCount On

      Create Table #Res
       (Name SysName,
        Rows Int,
        Reserved VarChar(20),
        Data VarChar(20),
        Index_Size VarChar(20),
        Unused VarChar(20))

        @More Bit,
        @CTableName As SysName

      Declare CTable Cursor
      Local Fast_Forward
        Table_Type = 'Base table'

      Set @More = 1
      Open CTable

      While (@More = 1)

          Fetch Next From CTable
          Into @CTableName

          If (@@Fetch_Status != 0)
            Set @More = 0      
            Insert Into #Res
            Execute sp_SpaceUsed @CTableName


      Select * From #Res
      Order By Cast(Left(Reserved, Len(Reserved) - 3) As Int) Desc

      Drop Table #Res

      Set NoCount Off

    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now