SQL Server 2016 SP_SpaceUsed 新参数@OneResultSet

这个新的参数@OneResultSet 就跟它的名字一样,将结果变成一个集合.

 

 

原来的SP_SpaceUsed将结果分成了两部分. 实际上使用sp_helptext 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.  
@mode varchar(11) = 'ALL',   -- Param. for specifying whether to calculate space usage for  
         -- local data only or remote data archive only or both.  
@oneresultset bit = 0    -- Param. for specifying whether to return only one result set     /*--------------------------定义---------------------------*/
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  
  ,@remotesql nvarchar(1000)  
  ,@isupdateusage bit  
  ,@errormessage nvarchar(4000)  
  ,@errornumber int  
  ,@errorseverity int  
  ,@errorstate int  
  ,@errorline int;  
  
DECLARE @summary_tmp_table table(  
   database_name nvarchar(128),  
   database_size varchar(18),  
   unallocated_space varchar(18),  
   reserved varchar(18),  
   data varchar(18),  
   index_size varchar(18),  
   unused varchar(18));  
  
DECLARE @detail_tmp_table table(  
   name nvarchar(128),  
   rows bigint,  
   reserved nvarchar(80),  
   data nvarchar(80),  
   index_size nvarchar(80),  
   unused nvarchar(80));  
  
/*  
** Check to see if user wants usages updated.  
*/  
if @updateusage is not null  
begin  
  select @updateusage=lower(@updateusage)  
  
  if @updateusage ='true'  
   begin  
   set @isupdateusage = 1  
  end  
  else  
  begin  
   if @updateusage = 'false'  
   begin  
    set @isupdateusage = 0  
   end  
   else  
   begin  
    raiserror(15143,-1,-1,@updateusage)  
    return(1)  
   end  
  end  
end  
  
/*  
** Validate the @mode parameter.  
*/  
IF UPPER(@mode) NOT IN('ALL', 'LOCAL_ONLY', 'REMOTE_ONLY')  
BEGIN  
  raiserror (14822, -1, -1, @mode);  
  return (1)  
END  
  
set nocount on  
  
/*  
** 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  
ELSE  
begin  
  
  /*  
  ** Now we know the object type is in ('U ','S ','V ','SQ','IT')  
  ** All types of objects always have physical size except for V (views). Only indexed views have actual sizes.  
  ** Since an un-indexed view does not occupy physical storage space, querying its space usage is meaningless.  
  */  
  IF @type = 'V '  
  begin  
   IF NOT EXISTS  
    (SELECT TOP 1 object_id  
    FROM sys.dm_db_partition_stats  
    WHERE object_id = @id) -- An un-indexed view DOES NOT have an row in sys.dm_db_partition_stats.  
   begin  
    SELECT  
     @objname AS name,  
     NULL AS rows,  
     NULL AS reserved,  
     NULL AS data,  
     '0 KB' AS index_size,  
     '0 KB' AS unused  
    return (0)  
   end  
  end  
end  
end  
  
/*  
**  Update usages if user specified to do so.  
*/  
  
if @isupdateusage = 1  
begin  
  if @objname is null  
   dbcc updateusage(0) with no_infomsgs  
  else  
   dbcc updateusage(0,@objname) with no_infomsgs  
  print ' '  
end  
  
/*  
**  If @id is null, then we want summary data.  
*/  
if @id is null  
begin  
/*  
** Calculate local space usage if mode is LOCAL_ONLY or ALL   
*/  
IF UPPER(@mode) IN ('ALL', 'LOCAL_ONLY')  
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 and semantic index internal tables are not considered "data", but is part of "index_size"  
      When it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236) Then 0  
      When a.type <> 1 and p.index_id < 2 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  
  
/*  
  ** Calculate the summary data and insert them into the cache table  
  ** 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)  
  ** note that unallocated space could not be negative  
  */  
  INSERT INTO @summary_tmp_table  
   SELECT   
    db_name(),  
    LTRIM(STR((CONVERT (dec (15,2),@dbsize) + CONVERT (dec (15,2),@logsize)) *  
     8192 / 1048576,15,2) + ' MB'),  
    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'),  
    LTRIM(STR(@reservedpages * 8192 / 1024.,15,0) + ' KB'),  
    LTRIM(STR(@pages * 8192 / 1024.,15,0) + ' KB'),  
    LTRIM(STR((@usedpages - @pages) * 8192 / 1024.,15,0) + ' KB'),  
    LTRIM(STR((@reservedpages - @usedpages) * 8192 / 1024.,15,0) + ' KB')  
END  
/*  
** Include remote results if the user specified no arguments , or remote only.  
*/  
IF UPPER(@mode) IN ('ALL', 'REMOTE_ONLY')  
BEGIN  
  /*  
  ** If the database is not stretched, it should not have a remote part, thus skipping remote results.  
  */  
  IF NOT EXISTS(SELECT * FROM sys.remote_data_archive_databases)  
  BEGIN  
   IF UPPER(@mode) = 'REMOTE_ONLY'  
   BEGIN  
    raiserror(14821, 16, 1);  
    return (1)  
   END  
  END  
  /*  
  ** Since the database is stretched and the remote database should exist, we should include remote results  
  */  
  ELSE  
  BEGIN  
  
   SET @remotesql = CONCAT(  
    N'sys.sp_spaceused_remote_data_archive  ',  
    @isupdateusage)  
   BEGIN TRY  
    INSERT INTO @summary_tmp_table EXECUTE(@remotesql);    /*------------------------返回的第一个表--------------------------*/     
   END TRY  
   BEGIN CATCH  
    set @errornumber = ERROR_NUMBER()  
    set @errorseverity = ERROR_SEVERITY()  
    set @errorstate = ERROR_STATE()  
    set @errorline = ERROR_LINE()  
    set @errormessage = ERROR_MESSAGE()  
    raiserror(14827, 16, 2, @errornumber, @errorseverity, @errorstate, @errorline, @errormessage)  
    IF UPPER(@mode) = 'REMOTE_ONLY'  
    BEGIN  
     return (1)  
    END  
   END CATCH  
  END  
END  
IF @oneresultset = 1  
BEGIN  
  SELECT  
   database_name = db_name(),  
   database_size = LTRIM (STR ((SUM  (CONVERT (dec (15, 2), SUBSTRING(s.database_size, 1, CHARINDEX(' ', s.database_size))))),15,2) + ' MB'),  
   'unallocated space' = LTRIM (STR ((SUM  (CONVERT (dec (15, 2), SUBSTRING(s.unallocated_space, 1, CHARINDEX(' ', s.unallocated_space))))),15,2) + ' MB'),  
   reserved = LTRIM (STR (SUM  (CAST (SUBSTRING(s.reserved, 1, CHARINDEX(' ', s.reserved)) AS bigint))) + ' KB'),  
   data = LTRIM (STR (SUM( CAST( SUBSTRING(s.data, 1, CHARINDEX(' ', s.data)) AS bigint))) + ' KB'),  
   index_size = LTRIM (STR (SUM( CAST( SUBSTRING(s.index_size, 1, CHARINDEX(' ', s.index_size)) AS bigint))) + ' KB'),  
   unused = LTRIM (STR (SUM( CAST( SUBSTRING(s.unused, 1, CHARINDEX(' ', s.unused)) AS bigint))) + ' KB')  
  FROM @summary_tmp_table AS s  
END  
ELSE  
BEGIN  
  SELECT  
   database_name = db_name(),  
   database_size = LTRIM (STR ((SUM  (CONVERT (dec (15, 2), SUBSTRING(s.database_size, 1, CHARINDEX(' ', s.database_size))))),15,2) + ' MB'),  
   'unallocated space' = LTRIM (STR ((SUM  (CONVERT (dec (15, 2), SUBSTRING(s.unallocated_space, 1, CHARINDEX(' ', s.unallocated_space))))),15,2) + ' MB')  
  FROM @summary_tmp_table AS s  
  SELECT  
   reserved = LTRIM (STR (SUM  (CAST (SUBSTRING(s.reserved, 1, CHARINDEX(' ', s.reserved)) AS bigint))) + ' KB'),  
   data = LTRIM (STR (SUM( CAST( SUBSTRING(s.data, 1, CHARINDEX(' ', s.data)) AS bigint))) + ' KB'),  
   index_size = LTRIM (STR (SUM( CAST( SUBSTRING(s.index_size, 1, CHARINDEX(' ', s.index_size)) AS bigint))) + ' KB'),  
   unused = LTRIM (STR (SUM( CAST( SUBSTRING(s.unused, 1, CHARINDEX(' ', s.unused)) AS bigint))) + ' KB')  
  FROM @summary_tmp_table AS s  
END  
END  
  
/*  
** We want a particular object.  
*/  
ELSE  
BEGIN  
/*  
** Include remote results if the user expects remote space usage.  
*/  
IF UPPER(@mode) = 'ALL' or UPPER(@mode) = 'REMOTE_ONLY'  
BEGIN  
  /*  
  ** If the object is stretched, we should include remote space usage into the result.  
  */  
  IF EXISTS (SELECT * FROM sys.remote_data_archive_tables WHERE object_id = @id AND remote_table_name IS NOT NULL)  
  BEGIN  
   SET @remotesql = CONCAT(  
    N'sys.sp_spaceused_remote_data_archive  ',  
    @isupdateusage,  
    N' , ',  
    @id)  
    BEGIN TRY  
     INSERT INTO @detail_tmp_table EXECUTE(@remotesql)  
    END TRY  
    BEGIN CATCH  
     set @errornumber = ERROR_NUMBER()  
     set @errorseverity = ERROR_SEVERITY()  
     set @errorstate = ERROR_STATE()  
     set @errorline = ERROR_LINE()  
     set @errormessage = ERROR_MESSAGE()  
     raiserror(14827, 16, 3, @errornumber, @errorseverity, @errorstate, @errorline, @errormessage)  
     IF UPPER(@mode) = 'REMOTE_ONLY'  
     BEGIN  
      return (1)  
     END  
    END CATCH  
   END  
  /*  
  ** If the object is not stretched, the object shouldn't have a remote part, thus skipping remote results.  
  */  
  ELSE  
  BEGIN  
   IF UPPER(@mode) = 'REMOTE_ONLY'  
   BEGIN  
    RAISERROR(14821, 16, 2);  
    RETURN (1)  
   END  
  END  
END  
  
/*  
** Calculate local space usage if mode is LOCAL_ONLY or ALL   
*/  
IF UPPER(@mode) IN ('ALL', 'LOCAL_ONLY')  
BEGIN  
  /*  
  ** Now calculate the summary data.   
  *  Note that LOB Data and Row-overflow Data are counted as Data Pages for the base table  
  *  For non-clustered indices they are counted towards the index 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 0  
    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,207,211,212,213,214,215,216,221,222,236)) > 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,207,211,212,213,214,215,216,221,222,236) AND p.object_id = it.object_id;  
  END  
  
  INSERT INTO @detail_tmp_table   /*------------------------返回的第二个表--------------------------*/      
   SELECT   
    OBJECT_NAME (@id),  
    CONVERT (char(20), @rowCount),  
    LTRIM (STR (@reservedpages * 8, 15, 0) + ' KB'),  
    LTRIM (STR (@pages * 8, 15, 0) + ' KB'),  
    LTRIM (STR ((CASE WHEN @usedpages > @pages THEN (@usedpages - @pages) ELSE 0 END) * 8, 15, 0) + ' KB'),  
    LTRIM (STR ((CASE WHEN @reservedpages > @usedpages THEN (@reservedpages - @usedpages) ELSE 0 END) * 8, 15, 0) + ' KB')  
END  
  
SELECT @objname AS name,  
CONVERT (char(20), SUM(rows)) AS rows,  
LTRIM (STR (SUM (CAST (SUBSTRING(reserved, 1, CHARINDEX(' ', reserved)) AS bigint))) + ' KB' ) AS reserved,  
LTRIM (STR (SUM (CAST (SUBSTRING(data, 1, CHARINDEX(' ', data)) AS bigint))) + ' KB') AS data,  
LTRIM (STR (SUM (CAST (SUBSTRING(index_size, 1, CHARINDEX(' ', index_size)) AS bigint))) + ' KB') AS index_size,  
LTRIM (STR (SUM (CAST (SUBSTRING(unused, 1, CHARINDEX(' ', unused)) AS bigint))) + ' KB') AS unused  
FROM @detail_tmp_table  
END  
  
return (0) -- sp_spaceused 

 

新的@OneResultSet使用之后呢?

实际的Sp_SpaceUsed的代码定义中

@oneresultset bit = 0    -- Param. for specifying whether to return only one result set 

嵌入使用sp_MSforeachdb,可以一次获取所有数据库的空间使用状况.

 

还是非常不方便,如果可以在一个resultset就好了. 请看下面的方法:

create table #spaceused 
(database_name varchar(100) 
,database_size varchar(20) 
,unallocated_space varchar(20) 
,reserved varchar(20) 
,data varchar(20) 
,index_size varchar(20) 
,unused varchar(20) 
) 
go
insert into #spaceused 
exec sp_MSforeachdb 
@command1= ' 
use [?] 
exec sp_spaceused @oneresultset=1' 
go
select 
database_name [Database Name] 
,cast(replace(database_size,'MB','') as decimal(10,2)) [Database Size (MB)] 
,cast(replace(unallocated_space,'MB','') as decimal(10,2)) [Free Space (MB)] 
,(cast(replace(reserved,'KB','') as int))/1024 [Reserved (MB)] 
,(cast(replace(data,'KB','') as int))/1024 [Data Space (MB)] 
,(cast(replace(index_size,'KB','') as int))/1024 [Index Space (MB)] 
,(cast(replace(unused,'KB','') as int))/1024 [Unused Space (MB)] 
from #spaceused 
order by [Unused Space (MB)] desc

 

有了参考,就好办了.  下面是我写的一个批处理,生成可以调整数据库大小修改的DBCC

WITH    tt 
          AS ( SELECT   database_name [Database Name] , 
                        CAST(REPLACE(database_size, 'MB', '') AS DECIMAL(10, 2)) [Database Size (MB)] , 
                        CAST(REPLACE(unallocated_space, 'MB', '') AS DECIMAL(10, 
                                                              2)) [Free Space (MB)] , 
                        ( CAST(REPLACE(reserved, 'KB', '') AS INT) ) / 1024 [Reserved (MB)] , 
                        ( CAST(REPLACE(data, 'KB', '') AS INT) ) / 1024 [Data Space (MB)] , 
                        ( CAST(REPLACE(index_size, 'KB', '') AS INT) ) / 1024 [Index Space (MB)] , 
                        ( CAST(REPLACE(unused, 'KB', '') AS INT) ) / 1024 [Unused Space (MB)] 
               FROM     #spaceused 
             ) 
    SELECT  DB_NAME(database_id) [Database Name] , 
            'DBCC SHRINKDATABASE(N''' + DB_NAME(database_id) + ''', 30 );' [Shrink Statement] 
    FROM    sys.databases 
    WHERE   database_id IN ( SELECT TOP ( 10 ) 
                                    DB_ID([Database Name]) 
                             FROM   tt 
                             ORDER BY [Unused Space (MB)] DESC );

 

注意收缩数据库的大小哦.