----------------------------------------------------------------------
--  This SP Captures Database Space/growth information for all databases
--  on this SQL Server. This information is inserted into table DASD,
--  then Disk Drive Space Information is retreived from table DrvSpace
--  and combined (updates) with the appropriate rows in table DASD.
--  Finally, table DASD is checked for a 'spacer line' for each database,
--  if absent (new database) one is added.  This 'spacer line' improves the 
--  'readability' of the DASD Database Space Reports.
----------------------------------------------------------------------
Create procedure sp_Monitor_Capture_DB_Space_Info
as
SET NOCOUNT ON
DECLARE @counter SMALLINT
DECLARE @holddate VARCHAR(20)
DECLARE @dbname VARCHAR(100)
DECLARE @total_DB_space VARCHAR(10)
DECLARE @sum_total_Drives_space int
DECLARE @sum_free_Drives_space int
DECLARE @sum_growth int
declare @bytesperpage	dec(15,0)
declare @pagesperMB	dec(15,0)
declare @summaxsize      int
select  @summaxsize  = 0

----------------------------------------------------------------------
-- Create Temp tables to hold Data Extracted by EXEC (string....) commands
----------------------------------------------------------------------
create table #holditems1 (dbsize_raw int, maxedsize int)
create table #holditems2 (group_name varchar(30), growth int, driveletter char(1))
create table #holditems3 (reserved int)

----------------------------------------------------------------------
-- Determine Pages per MB for this SQL Server and compute createDTM
----------------------------------------------------------------------
select @bytesperpage = low
	from master.dbo.spt_values
	where number = 1
	  and type = 'E'
select @pagesperMB = 1048576 / @bytesperpage
select @holddate=convert(char(08),getdate(),112)+convert(char(08),getdate(),108)


----------------------------------------------------------------------
-- Here we begin the looping through all the individual databases
----------------------------------------------------------------------
SET @dbname = 'none'
SELECT @counter=MAX(dbid) FROM master..sysdatabases
WHILE @counter > 0
BEGIN
 SELECT @dbname=name FROM master..sysdatabases WHERE dbid=@counter
 IF (SELECT @dbname) = 'none'
    BEGIN
     GOTO NEXTONE
    END
 
----------------------------------------------------------------------
--  This clears the temp tables 
----------------------------------------------------------------------
  begin
  truncate table #holditems1
  truncate table #holditems2
  truncate table #holditems3
  end

----------------------------------------------------------------------
-- These EXEC statements extract data from an individual database and 
-- place it in the #holditems tables
----------------------------------------------------------------------
   EXEC ('insert #holditems1 select sum(convert(dec(15),size)), sum(maxsize)
	   from ' + '[' + @dbname + ']' + '..[sysfiles]
          where (status & 64 = 0)')
   EXEC ('insert #holditems2 select name, growth, substring(filename,1,1)
	   from ' + '[' + @dbname + ']' + '..[sysfiles]
          where (status & 64 = 0)')
   EXEC ('insert #holditems3 select sum(reserved)
   	   from ' + '[' + @dbname + ']' + '..[sysindexes]
  	  where indid in (0, 1, 255)')

----------------------------------------------------------------------
-- Here we set @summaxsize. If #holditems1.maxsize < 0, there is no Autogrowth.
-- This value (usually -1) is transferred to @summaxsize. If Autogrowth is on
-- and maxedsize has a value > 0, this value is divided by pagesperMB and 
-- transferred to @summaxsize.
----------------------------------------------------------------------
   if (select maxedsize from #holditems1) > 0
    begin
     select @summaxsize=sum(maxedsize)/@pagesperMB from #holditems1
    end
   else
     begin
      select @summaxsize=maxedsize from #holditems1
     end

----------------------------------------------------------------------
-- Here we compute @total_DB_space and @sum_gowqth
-----------------------------------------------------------------------
   select @total_DB_space=ltrim(str(dbsize_raw / @pagesperMB,15,2)) 
     from #holditems1
   select @sum_growth = sum(growth) 
     from #holditems2

----------------------------------------------------------------------
-- Here we insert data into the DASD table.
----------------------------------------------------------------------
   insert msdb..DASD 
   select  @holddate,
	   @@servername,
   	   @dbname,
	   'none',
	   'none',
	   @total_DB_space, 
	   'data only',
	   ltrim(str(@total_DB_space - (select convert(dec(15),reserved)
   				from #holditems3)/@pagesperMB,15,2)),
           '  ',
           '  ',
           'none',
	   @summaxsize,
	   @sum_growth

----------------------------------------------------------------------
--  Here we update these newly inserted rows with drive space information
----------------------------------------------------------------------
select @sum_total_Drives_space = sum(t.MB_Total)
  from tempdb..DrvSpace t
 where t.DriveLetter in (select distinct driveletter from #holditems2)
 
select @sum_free_Drives_space = sum(t.MB_Free)
 from tempdb..DrvSpace t
 where t.DriveLetter in (select distinct driveletter from #holditems2)

update [msdb].[dbo].[DASD]
 set total_drive_space = @sum_total_Drives_space,
     free_drive_space  = @sum_free_Drives_space
 from msdb..DASD d
 where db_name = @dbname
   and @holddate = d.createDTM

----------------------------------------------------------------------
--  This checks the DASD table for the spacer line used in DASD Reports. 
--  If it is missing it inserts it into DASD.
----------------------------------------------------------------------
  if (select count(*) from msdb..DASD 
      where createDTM = '2020122504:30:03' and db_name = @dbname) = 0
    begin
       insert msdb..DASD values ('2020122504:30:03','',@dbname,
                                 '','','','','','','','','','')
    end
 
----------------------------------------------------------------------
--  This is the bottom of the loop, we increment the loop counter '@counter' 
--  and go back to the top of the loop to process another database
----------------------------------------------------------------------
NEXTONE:
SET @counter = @counter - 1
SET @dbname = 'none'
END

----------------------------------------------------------------------
--  Here the loop through all the databases is complete, just extra 
--  cautious to clean up.
----------------------------------------------------------------------
drop table #holditems1
drop table #holditems2
drop table #holditems3
go