How to find the size of the table.

• SQL_Hunt

  SSC-Dedicated

  Points: 33518

  More actions

  January 21, 2010 at 7:29 am

  #138395

  Hello Experts

  I am willing to know if there is any T-SQL query available to find out the size of the table.

  Please assist me in this regard.

  Thanks in advance,

  Thanks.

• José.Cruz

  Right there with Babe

  Points: 722

  More actions

  January 21, 2010 at 7:57 am

  #1106712

  Hi there,

  Can you take a look at sp_spaceused and see if it gives all the information you want to know about the table?

  José Cruz

• murthykalyani

  SSCarpal Tunnel

  Points: 4500

  More actions

  January 21, 2010 at 8:05 am

  #1106720

  Sourav-657741 (1/21/2010)

  ---

  Hello Experts

  I am willing to know if there is any T-SQL query available to find out the size of the table.

  This is the query

  select object_name(s2.id) as TableName,(sum(s1.reserved*8) )TableSize_In_KB,sum(s1.rowcnt) as NumberofRows,

  DataSize_KB= case (s1.indid)

  when 255 then (((sum(s1.reserved*8)-sum(s1.dpages*8))- (sum(s1.reserved*8)-sum(s1.used*8))) )

  else sum(s1.dpages*8)

  end ,

  IndexSize_KB = case (s1.indid)

  when 255 then 0

  else (((sum(s1.reserved*8)-sum(s1.dpages*8))- (sum(s1.reserved*8)-sum(s1.used*8))))

  end,sum(s1.used*8) UsedSpace_KB,

  ((sum(s1.reserved*8)-sum(s1.used*8)) )FreeSpace_KB

  from sysindexes s1 inner join sysobjects s2

  on s1.id = s2.id

  where s2.type = 'U' and ((s1.indid = 1) or (s1.indid =0 or s1.indid = 255))

  group by s2.id,s1.indid ORDER BY TableSize_In_KB desc

• SQL_Hunt

  SSC-Dedicated

  Points: 33518

  More actions

  January 21, 2010 at 9:20 am

  #1106789

  Thanks Kalyani for your assistance.

  I executed the query and found my desired results. [:)]

  Thanks.