檢視SQL SERVER表的空間使用情況(續)

 

檢視了系統的Sp_SpaceUsed過程，發現實際資料來源於sys.dm_db_partition_stats的DMV，因此，也可透過以下SQL語句直接檢視。

 

select b.name IndexName,

 OBJECT_NAME(object_id) as TableName,

 case index_id when 0 then '堆'

            when 1 then '聚集索引'

            else '非聚集索引'

   end as 型別,

 in_row_data_page_count*8/1024 as DataUsed_MB,

 in_row_reserved_page_count*8/1024 as DataTotal_MB,

 lob_used_page_count*8/1024 as LobUsed_MB,

 lob_reserved_page_count*8/1024 as LobTotal_MB,

 row_overflow_used_page_count*8/1024 as Overflow_MB,

 row_overflow_reserved_page_count*8/1024 as OverflowTotal_MB,

 used_page_count*8/1024 as Used_MB,

 reserved_page_count*8/1024 as Total_MB,

 row_count as RowsCount

 

  from sys.dm_db_partition_stats a join sys.sysindexes b

       on a.object_id=b.id and a.index_id=b.indid

  order by Total_MB desc

 

附：Sp_SpaceUsed程式碼

create procedure sys.sp_spaceused --- 2003/05/19 14:00 

@objname nvarchar(776) = null,  -- The object we want size on. 

@updateusage varchar(5) = false  -- Param. for specifying that 

     -- usage info. should be updated. 

as 

 

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 

 

/* 

**  Check to see if user wants usages updated. 

*/ 

 

if @updateusage is not null 

 begin 

  select @updateusage=lower(@updateusage) 

 

  if @updateusage not in ('true','false') 

   begin 

    raiserror(15143,-1,-1,@updateusage) 

    return(1) 

   end 

 end 

/* 

**  Check to see that the objname is local. 

*/ 

if @objname IS NOT NULL 

begin 

 

 select @dbname = parsename(@objname, 3) 

 

 if @dbname is not null and @dbname <> db_name() 

  begin 

   raiserror(15250,-1,-1) 

   return (1) 

  end 

 

 if @dbname is null 

  select @dbname = db_name() 

 

 /* 

 **  Try to find the object. 

 */ 

 SELECT @id = object_id, @type = type FROM sys.objects WHERE object_id = object_id(@objname) 

 

 -- Translate @id to internal-table for queue 

 IF @type = 'SQ' 

  SELECT @id = object_id FROM sys.internal_tables WHERE parent_id = @id and internal_type = 201 --ITT_ServiceQueue 

 

 /* 

 **  Does the object exist? 

 */ 

 if @id is null 

  begin 

   raiserror(15009,-1,-1,@objname,@dbname) 

   return (1) 

  end 

 

 -- Is it a table, view or queue? 

 IF @type NOT IN ('U ','S ','V ','SQ','IT') 

 begin 

  raiserror(15234,-1,-1) 

  return (1) 

 end 

end 

 

/* 

**  Update usages if user specified to do so. 

*/ 

 

if @updateusage = 'true' 

 begin 

  if @objname is null 

   dbcc updateusage(0) with no_infomsgs 

  else 

   dbcc updateusage(0,@objname) with no_infomsgs 

  print ' ' 

 end 

 

set nocount on 

 

/* 

**  If @id is null, then we want summary data. 

*/ 

if @id is null 

begin 

 select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) 

  , @logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) 

  from dbo.sysfiles 

 

 select @reservedpages = sum(a.total_pages), 

  @usedpages = sum(a.used_pages), 

  @pages = sum( 

    CASE 

     -- XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size" 

     When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0 

     When a.type <> 1 Then a.used_pages 

     When p.index_id < 2 Then a.data_pages 

     Else 0 

    END 

   ) 

 from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id 

  left join sys.internal_tables it on p.object_id = it.object_id 

 

 /* unallocated space could not be negative */ 

 select  

  database_name = db_name(), 

  database_size = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize))  

   * 8192 / 1048576,15,2) + ' MB'), 

  'unallocated space' = ltrim(str((case when @dbsize >= @reservedpages then 

   (convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages))  

   * 8192 / 1048576 else 0 end),15,2) + ' MB') 

 

 /* 

 **  Now calculate the summary data. 

 **  reserved: sum(reserved) where indid in (0, 1, 255) 

 ** data: sum(data_pages) + sum(text_used) 

 ** index: sum(used) where indid in (0, 1, 255) - data 

 ** unused: sum(reserved) - sum(used) where indid in (0, 1, 255) 

 */ 

 select 

  reserved = ltrim(str(@reservedpages * 8192 / 1024.,15,0) + ' KB'), 

  data = ltrim(str(@pages * 8192 / 1024.,15,0) + ' KB'), 

  index_size = ltrim(str((@usedpages - @pages) * 8192 / 1024.,15,0) + ' KB'), 

  unused = ltrim(str((@reservedpages - @usedpages) * 8192 / 1024.,15,0) + ' KB') 

end 

 

/* 

**  We want a particular object. 

*/ 

else 

begin 

 /* 

 ** Now calculate the summary data.  

 *  Note that LOB Data and Row-overflow Data are counted as Data Pages. 

 */ 

 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') 

 

end 

 

return (0) -- sp_spaceused