ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 查看SQL SERVER表的空间使用情况(续)

查看SQL SERVER表的空间使用情况(续)

原创 Linux操作系统 作者:cow977 时间:2012-07-20 18:01:12 0 删除 编辑

 

查看了系统的Sp_SpaceUsed过程,发现实际数据来源于sys.dm_db_partition_statsDMV,因此,也可通过以下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 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/81227/viewspace-736171/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-03-02

  • 博文量
    629
  • 访问量
    684073