need way to get disk space used by table

• chris 50844

  Newbie

  Points: 4

  More actions

  March 12, 2010 at 8:52 am

  #216491

  When I right-click a table in Mgmt Studio and select Properties, under Storage I have a value labeled General / Data space.

  Is this info available from T-SQL?

• Luke L

  SSC-Dedicated

  Points: 35547

  More actions

  March 12, 2010 at 9:10 am

  #1132788

  Have a look at the sp_spaceused system stored procedure in BOL.

  I think it will get you want you want.

  -Luke.

  To help us help you read this[/url]For better help with performance problems please read this[/url]

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  March 12, 2010 at 9:13 am

  #1132794

  This script will give you a breakdown of the space usage of all tables in a database.

  Script to analyze table space usage

  http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  March 12, 2010 at 9:15 am

  #1132797

  You might want to use this created by RBarryYoung

  CREATE VIEW vwTableInfo

  --Author: 16-March-2008, RBarryYoung SSC

  AS

  SELECT SCHEMA_NAME(tbl.schema_id) as [Schema]

  , tbl.Name

  , Coalesce((Select pr.name

  From sys.database_principals pr

  Where pr.principal_id = tbl.principal_id)

  , SCHEMA_NAME(tbl.schema_id)) as [Owner]

  , tbl.max_column_id_used as [Columns]

  , CAST(CASE idx.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [HasClusIdx]

  , Coalesce( ( select sum (spart.rows) from sys.partitions spart

  where spart.object_id = tbl.object_id and spart.index_id < 2), 0) AS [RowCount]

  , Coalesce( (Select Cast(v.low/1024.0 as float)

  * SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)

  FROM sys.indexes as i

  JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id

  JOIN sys.allocation_units as a ON a.container_id = p.partition_id

  where i.object_id = tbl.object_id )

  , 0.0) AS [IndexKB]

  , Coalesce( (Select Cast(v.low/1024.0 as float)

  * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)

  FROM sys.indexes as i

  JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id

  JOIN sys.allocation_units as a ON a.container_id = p.partition_id

  where i.object_id = tbl.object_id)

  , 0.0) AS [DataKB]

  , tbl.create_date, tbl.modify_date

  FROM sys.tables AS tbl

  INNER JOIN sys.indexes AS idx ON (idx.object_id = tbl.object_id and idx.index_id < 2)

  INNER JOIN master.dbo.spt_values v ON (v.number=1 and v.type='E')

  -- Run as:

  --SELECT * FROM vwTableInfo

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• chris 50844

  Newbie

  Points: 4

  More actions

  March 12, 2010 at 12:01 pm

  #1132913

  --- Thanks. All very useful replies.