SPACE Table

space.sql

use DBNAME
go
 
declare @id int   -- The object id that takes up space  
  ,@type character(2) -- The object type.  
  ,@pages bigint   -- Working variable for size calc.  
  ,@dbname sysname  
  ,@dbsize bigint  
  ,@logsize bigint  
  ,@reservedpages  bigint  
  ,@usedpages  bigint  
  ,@rowCount bigint  
  
   SELECT @id = object_id, @type = type FROM sys.objects WHERE object_id = object_id('TABLENAME')  
 
 SELECT   
  @reservedpages = SUM (reserved_page_count),  
  @usedpages = SUM (used_page_count),  
  @pages = SUM (  
   CASE  
    WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)  
    ELSE lob_used_page_count + row_overflow_used_page_count  
   END  
   ),  
  @rowCount = SUM (  
   CASE  
    WHEN (index_id < 2) THEN row_count  
    ELSE 0  
   END  
   )  
 FROM sys.dm_db_partition_stats  
 WHERE object_id = @id;  
  
 /*  
 ** Check if table has XML Indexes or Fulltext Indexes which use internal tables tied to this table  
 */  
 IF (SELECT count(*) FROM sys.internal_tables WHERE parent_id = @id AND internal_type IN (202,204,211,212,213,214,215,216)) > 0   
 BEGIN  
  /*  
  **  Now calculate the summary data. Row counts in these internal tables don't   
  **  contribute towards row count of original table.  
  */  
  SELECT   
   @reservedpages = @reservedpages + sum(reserved_page_count),  
   @usedpages = @usedpages + sum(used_page_count)  
  FROM sys.dm_db_partition_stats p, sys.internal_tables it  
  WHERE it.parent_id = @id AND it.internal_type IN (202,204,211,212,213,214,215,216) AND p.object_id = it.object_id;  
 END  
  
 SELECT   
  name = OBJECT_NAME (@id),  
  rows = convert (char(11), @rowCount),  
  reserved = LTRIM (STR (@reservedpages * 8, 15, 0) + ' KB'),  
  data = LTRIM (STR (@pages * 8, 15, 0) + ' KB'),  
  index_size = LTRIM (STR ((CASE WHEN @usedpages > @pages THEN (@usedpages - @pages) ELSE 0 END) * 8, 15, 0) + ' KB'),  
  unused = LTRIM (STR ((CASE WHEN @reservedpages > @usedpages THEN (@reservedpages - @usedpages) ELSE 0 END) * 8, 15, 0) + ' KB')