• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 814
  • Last Modified:

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

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.

0
gwalkeriq
Asked:
gwalkeriq
3 Solutions
 
PaulKeatingCommented:
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.
0
 
ABaruhCommented:
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
0
 
SjoerdVerweijCommented:
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))


  Declare
    @More Bit,
    @CTableName As SysName

  Declare CTable Cursor
  Local Fast_Forward
  For
  Select
    Table_Name
  From
    Information_Schema.Tables
  Where
    Table_Type = 'Base table'


  Set @More = 1
  Open CTable


  While (@More = 1)
    Begin

      Fetch Next From CTable
      Into @CTableName

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

    End


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

  Drop Table #Res


  Set NoCount Off
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now