USP_DATABASEGROWTHMANAGEMENT_GETSUMMARY

CREATE procedure dbo.[USP_DATABASEGROWTHMANAGEMENT_GETSUMMARY]
as begin
  set nocount on;

  declare @DBDATAFILES bigint;
  declare @DBLOGFILES bigint;
  declare @RESERVEDPAGES bigint;
  declare @USEDPAGES bigint;
  declare @PAGES bigint;
  declare @DBSIZEKB bigint;
  declare @DBRESERVEDKB bigint;
  declare @DBUSEDKB bigint;
  declare @DBDATAKB bigint;
  declare @DBINDEXKB bigint;
  declare @DBUNUSEDKB bigint;
  declare @DBLOGKB bigint;
  declare @DBUNALLOCATEDKB bigint;
  declare @BUSINESSPROCESSPARAMETERSETID uniqueidentifier;
  declare @LASTRUN datetime;
  declare @STATUSCODE tinyint;
  declare @TABLESTOANALYZE integer;
  declare @TABLESANALYZED integer;

  -- this comes from sp_spaceused, modified slightly to use sys.database_files instead of the deprecated sysfiles...


  select @DBDATAFILES = sum(convert(bigint, case when type = 0 then size else 0 end)),
         @DBLOGFILES  = sum(convert(bigint, case when type = 1 then size else 0 end))
  from sys.database_files;

  -- this also comes from sp_spaceused, modified slightly for formatting...


  select
    @RESERVEDPAGES = sum(a.total_pages),
    @USEDPAGES = sum(a.used_pages),
    @PAGES = sum(case 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 as p
  inner join sys.allocation_units as a on p.partition_id = a.container_id
  left outer join sys.internal_tables it on p.object_id = it.object_id;

  -- this also comes from sp_spaceused, modified slightly to return values in KB, rather than formatting them as decimal MB...


  select
    @DBDATAKB = @PAGES * (8192 / 1024),
    @DBINDEXKB = (@USEDPAGES - @PAGES) * (8192 / 1024),
    @DBUNUSEDKB = (@RESERVEDPAGES - @USEDPAGES) * (8192 / 1024),
    @DBUNALLOCATEDKB = case when @DBDATAFILES >= @RESERVEDPAGES then (@DBDATAFILES - @RESERVEDPAGES) * (8192 / 1024) else 0 end,
    @DBLOGKB = @DBLOGFILES * (8192 / 1024);

  -- according to http://sqlserverzest.com/2013/06/28/sql-server-understanding-sp_spaceused-results-for-database-size-information/


  set @DBUSEDKB = @DBDATAKB + @DBINDEXKB;
  set @DBRESERVEDKB = @DBUSEDKB + @DBUNUSEDKB;
  set @DBSIZEKB = @DBRESERVEDKB + @DBUNALLOCATEDKB + @DBLOGKB;

  select top 1
    @BUSINESSPROCESSPARAMETERSETID = [ID],
    @TABLESANALYZED = [TABLESANALYZED],
    @TABLESTOANALYZE = [TABLESTOANALYZE]
  from dbo.[DATABASEGROWTHANALYSISPROCESS];

  set @STATUSCODE = 3; -- undefined


  if exists (select top 1 1 from dbo.[BUSINESSPROCESSSTATUS] where [BUSINESSPROCESSCATALOGID] = '23C3CAE9-FEC7-4EAB-BA79-E50C997A5BBF')
    select top 1
      @STATUSCODE = [STATUSCODE],
      @LASTRUN = [STARTEDON]
    from dbo.[BUSINESSPROCESSSTATUS]
    where [BUSINESSPROCESSCATALOGID] = '23C3CAE9-FEC7-4EAB-BA79-E50C997A5BBF'
    order by [STARTEDON] desc;

  select
    db_id() as [DBID],
    db_name() as [DBNAME],
    @DBSIZEKB as [DBSIZEKB],
    @DBRESERVEDKB as [DBRESERVEDKB],
    @DBUSEDKB as [DBUSEDKB],
    @DBDATAKB as [DBDATAKB],
    @DBINDEXKB as [DBINDEXKB],
    @DBUNUSEDKB as [DBUNUSEDKB],
    @DBLOGKB as [DBLOGKB],
    @DBUNALLOCATEDKB as [DBUNALLOCATEDKB],
    count(*) as [BBTABLES],
    sum(cast([DATAKB] as bigint) + cast([INDEXKB] as bigint)) as [BBUSEDKB],
    sum(cast([RESERVEDKB] as bigint)) as [BBRESERVEDKB],
    sum(cast([DATAKB] as bigint)) as [BBDATAKB],
    sum(cast([INDEXKB] as bigint)) as [BBINDEXKB],
    sum(cast([UNUSEDKB] as bigint)) as [BBUNUSEDKB],
    @BUSINESSPROCESSPARAMETERSETID as [BUSINESSPROCESSPARAMETERSETID],
    @LASTRUN as [LASTRUN],
    @STATUSCODE as [STATUSCODE],
    @TABLESTOANALYZE as [TABLESTOANALYZE],
    @TABLESANALYZED as [TABLESANALYZED]
  from dbo.[DATABASESPACEUSED];

  return 0;
end