Log and report on table growth for trending

--############################################################################################################################
--
 --This script is being offered for public use and as such is being offered as untested and unverified.
 --Please use this script at your own risk, as I take NO responsibility for it's use elsewhere in environments 
 --that are NOT under my control. 
 --Redistribution for sale of [dba_table_growth_report], in whole or in part, is prohibited! 
 
 --Always ensure that you run such scripts in test prior to production and perform due diligence as to whether they meet yours, 
 --or your company needs!
--
--############################################################################################################################
--
-- Author:Haden Kingsland
--
-- Date:3rd May 2016
--
-- Description :To report on database table growth over a rolling period,
--based upon the data held in the following <your admin database>
--table... dba.DB_TableSpace.
--The data for these tables being populated daily by the following
--SQL Agent job...
--
--Monitor Database Table Size
--
--...which both run at 07:45 every morning.
--
-- Modification History
-- ####################
--
---#######################################
-- 1 -- Create the stats collection table
---##################################################################################################################
--
-- you will need to create this table before you create the procedure!!!!
--
---##################################################################################################################
--
--USE [<your admin database>]
--GO

--/****** Object:  Table [dba].[TableSpace] ******/--IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[<your admin schema>].[DB_TableSpace]') AND type in (N'U'))
--DROP TABLE [<your admin schema>].[DB_TableSpace]
--GO

--USE [<your admin database>]
--GO

--/****** Object:  Table [<your admin schema>].[DB_TableSpace]    Script Date: 11/01/2012 11:15:52 ******/--SET ANSI_NULLS ON
--GO

--SET QUOTED_IDENTIFIER ON
--GO

--CREATE TABLE [<your admin schema>].[DB_TableSpace](
--[RunDate] [datetime] NOT NULL,
--[DatabaseName] [nvarchar](128) NULL,
--[SchemaName] [nvarchar](128) NULL,
--[TableName] [nvarchar](128) NOT NULL,
--[NumRows] [int] NOT NULL,
--[Reserved] [nchar](16) NOT NULL,
--[DataUsed] [nchar](16) NOT NULL,
--[IndexUsed] [nchar](16) NOT NULL,
--[Unused] [nchar](16) NOT NULL
--) ON [PRIMARY]

--GO
--ALTER TABLE [<your admin schema>].[DB_TableSpace] 
--ADD CONSTRAINT [DF_TableSpace_RunDate] 
--DEFAULT (getdate()) FOR [RunDate]
--GO
--
---######################################
-- 2 -- Collect the table size statistics
---##################################################################################################################
--
-- You will also need to create the following stored procedure in your admin database, that you can then either run 
-- on an adhoc basis, or schedule to run daily via the SQL Server Agent.
--
---##################################################################################################################
--
--use [DBAdmin]

create PROCEDURE [<your admin schema>].[dba_find_tableSpace]
AS

BEGIN

SET NOCOUNT ON;

DECLARE @SQL varchar(8000)

SELECT @SQL = '
IF ''@'' <> ''model'' AND ''@'' <> ''tempdb''
BEGIN
USE [@] EXECUTE sp_MSForEachTable ''INSERT INTO DBAdmin.dba.DB_TableSpace 
(TableName, NumRows, Reserved, DataUsed, IndexUsed, Unused) EXEC sp_spaceused ''''?'''';
UPDATE [<your admin database>].[<your admin schema>].DB_TableSpace SET SchemaName = LEFT(''''?'''', CHARINDEX(''''.'''', ''''?'''', 1) - 2) 
WHERE SchemaName IS NULL;
UPDATE [<your admin database>].[<your admin schema>].DB_TableSpace SET DatabaseName = ''''@'''' WHERE DatabaseName IS NULL; ''
END
'

EXEC sp_MSforeachdb @SQL, '@'

UPDATE [<your admin database>].[<your admin schema>].DB_TableSpace
SET SchemaName = REPLACE(SchemaName, '[', '')

END;

---##########################
-- 3 -- HTML Reporting
---##################################################################################################################
-- Usage...
--
-- Then, create this procedure to do the alerting. Again you can run it on an adhoc basis, or schedule it
-- via a SQL Agent job.
--
-- exec <your admin database>.<your admin schema>.dba_table_growth_report 15, 'hkingsland@mycompany.com'
--
---##################################################################################################################

USE [<your admin database>]
GO
/****** Object:  StoredProcedure [<your admin schema>].[dba_table_growth_report] ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create procedure [<your admin schema>].[dba_table_growth_report]
 (
    @days int,-- number of days to report on
    @recipient_list varchar(2000) -- list of people to email
 )
as

BEGIN

DECLARE@MailProfileName VARCHAR(50),
@ERR_MESSAGE varchar(200),
@ERR_NUM int,
@MESSAGE_BODY varchar(2000),
@MESSAGE_BODY2 varchar(1000),
@p_error_description varchar(300),
@NewLine CHAR(2),
@Q CHAR(1),
@tableHTML VARCHAR(MAX),
@tableHTML2 VARCHAR(MAX),
@lineHTML VARCHAR(MAX),
@lineHTML2 VARCHAR(MAX),
@start_table VARCHAR(MAX),
@start_table2 VARCHAR(MAX),
@TR varchar(20),
@END varchar(30),
@END_TABLE varchar(30),
@ENDTAB varchar(20),
--@recipient_listvarchar(1000),
@email varchar(100),
@value varchar(30),
@mailsubject varchar(200),
@propertyid int,
@userid bigint, 
@property_value varchar(1000),
@output VARCHAR(1000),
@failsafe VARCHAR(100), -- failsafe operator to email
@type varchar(10), 
@dbname varchar(100),
@tablename varchar(100),
@minrows bigint,
@maxrows bigint,
@rowchange bigint,
@GrowthAmt varchar(100),
@startDate datetime,
@endDate datetime,
@td varchar(25);

SET @NewLine = CHAR(13) + CHAR(10) 
SET @Q = CHAR(39) 

-- initialize variables 

set @lineHTML = '' 
set @tableHTML = ''
set @start_table = ''
--set @days = 10

SET @tableHTML =
'<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Frameset// EN">' +
'<html>' +
'<LANG="EN">' +
'<head>' +
'<TITLE>SQL Administration</TITLE>' +
'</head>' +
'<body>'

set @start_table = '<font color="black" face="Tahoma" >' + 
'<CENTER>' + 
'<H1><font size=4>Table growth for the '+ @@servername + ' instance on ' + 
CONVERT(VARCHAR(11),GETDATE(),113) + '</font></H1>' +
'<table border="1">' +
'<tr BGCOLOR="green">' + 
-- list all table headers here
'<th BGCOLOR="#0066CC" width="100%" colspan="7">Database Growth Details</th>'+'</tr>' + 
'<tr>' + 
'<th BGCOLOR="#99CCFF">Start Date</th>' + 
'<th BGCOLOR="#99CCFF">End Date</th>' +
'<th BGCOLOR="#99CCFF">Database Name</th>' + 
'<th BGCOLOR="#99CCFF">Table Name</th>' +
'<th BGCOLOR="#99CCFF">Start Rows</th>' +
'<th BGCOLOR="#99CCFF">End Rows</th>' + 
'<th BGCOLOR="#99CCFF">Amount or row change over period</th>' +
'</tr>'

SET @TR = '</tr>'
SET @ENDTAB = '</table></font>'
--SET @END = '</table></font></body></html>'
SET @END_TABLE = '</table></font>'
SET @END = '</body></html>'

SET @mailsubject   = 'Database table growth trends for ' + @@servername + ' on ' + CONVERT(VARCHAR(11),GETDATE(),113)

SELECT @MailProfileName = name
FROM msdb.dbo.sysmail_profile WITH (NOLOCK)
where name like '<operator wild card>%'

PRINT @MailProfileName

BEGIN TRY

DECLARE build_report CURSOR
FOR
select   a.mindate, --as 'Min Date',
         b.maxdate, --as 'Max Date',
         a.DatabaseName,
         b.TableName,
         a.minrows,
         b.maxrows,
         (b.maxrows - a.minrows) --as 'Amount or row change over period'
from     (select   distinct min(RunDate) as mindate,
                            DatabaseName,
                            --min(SchemaName),
                            TableName,
                            min(NumRows) as minrows
          from     <your admin database>.<your admin schema>.DB_TableSpace
          where RunDate < GETDATE() - @days
          --where TableName = 'dbcc_history'
          group by DatabaseName, TableName
          ) as a
         inner join
         (select   distinct max(rundate) as maxdate,
                            DatabaseName,
                            --max(SchemaName),
                            TableName,
                            max(NumRows) as maxrows
          from     <your admin database>.<your admin schema>.DB_TableSpace
          --where TableName = 'dbcc_history'
          group by DatabaseName, TableName
          ) as b
         on (a.tablename = b.tablename
             and a.DatabaseName = b.DatabaseName)
order by a.DatabaseName;

-- Open the cursor.
OPEN build_report;

-- Loop through the update_stats cursor.

FETCH NEXT
   FROM build_report
   INTO  @startDate, 
   @endDate,
   @dbname,
   @tablename,
   @minrows,
   @maxrows,
   @rowchange

--PRINT 'Fetch Status is ... ' + CONVERT(VARCHAR(10),@@FETCH_STATUS)

WHILE @@FETCH_STATUS <> -1 -- Stop when the FETCH statement fails or the row is beyond the result set
BEGIN

IF @@FETCH_STATUS = 0 -- to ignore -2 status "The row fetched is missing"
BEGIN

set @lineHTML = RTRIM(LTRIM(@lineHTML)) + 
'<tr>' + 
'<td>' +  ISNULL(left( convert (char(20) ,@startDate, 113 ), 17),'NOVAL') + '</td>' +
'<td>' +  ISNULL(left( convert (char(20) ,@endDate, 113 ), 17),'NOVAL') + '</td>' +
'<td>' +  ISNULL(cast(@dbname as varchar(50)),'NOVAL') + '</td>' +
'<td>' +  ISNULL(cast(@tablename as varchar(50)),'NOVAL') + '</td>' +
'<td>' +  ISNULL(cast(@minrows as varchar(10)),'NOVAL') + '</td>' +
'<td>' +  ISNULL(cast(@maxrows as varchar(10)),'NOVAL') + '</td>' +
'<td>' +  ISNULL(cast(@rowchange as varchar(10)),'NOVAL') + '</td>' +
+ '</tr>'

set @lineHTML = REPLACE( @lineHTML, '<td>', '<td BGCOLOR=#33FF99>' );

print @lineHTML

END

FETCH NEXT
   FROM build_report
   INTO  @startDate, 
   @endDate,
   @dbname,
   @tablename,
   @minrows,
   @maxrows,
   @rowchange

END

-- Close and deallocate the cursor.

CLOSE build_report;
DEALLOCATE build_report;

-- 03/02/2012 -- changed to check the contens of the @linehtml variable, so that it will not generate an email if there
-- is not data to display.

IF (@lineHTML != '' and @lineHTML is not NULL)
BEGIN

set @tableHTML = RTRIM(LTRIM(@tableHTML)) + @start_table + RTRIM(LTRIM(@lineHTML)) + @END_TABLE + @END

-- as the <td> tags are auto-generated, I need to replace then with a new <td>
-- tag including all the required formatting.

--set @tableHTML = REPLACE( @tableHTML, '<td>', '<td BGCOLOR=yellow>' );

print @tableHTML

IF @recipient_list IS NULL 
or @recipient_list = ''
BEGIN

SELECT @recipient_list = email_address
FROM msdb..sysoperators
WHERE name = '<your operator here>' -- Name of main required operator

IF @recipient_list IS NULL
BEGIN

EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
 N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
 N'AlertFailSafeOperator',
 @failsafe OUTPUT,
 N'no_output'

SELECT @recipient_list = email_address
FROM msdb..sysoperators
WHERE name = @failsafe
                             
END
END

PRINT @recipient_list

EXEC msdb.dbo.sp_send_dbmail
@profile_name = @MailProfileName,
--@recipients = 'hkingsland@laingorourke.com',
@recipients = @recipient_list,
@body_format = 'HTML',
@importance = 'HIGH',
@body = @tableHTML,
@subject = @mailsubject

END

END TRY

BEGIN CATCH

print 'Error Code is ... ' + RTRIM(CONVERT(CHAR(10),@ERR_NUM)) + ' Error Message is ... ' + @ERR_MESSAGE

SELECT @ERR_MESSAGE = ERROR_MESSAGE(), @ERR_NUM = ERROR_NUMBER();
SET @MESSAGE_BODY='Error running the ''Database Growth Report'' ' 
+  '. Error Code is ... ' + RTRIM(CONVERT(CHAR(10),@ERR_NUM)) + ' Error Message is ... ' + @ERR_MESSAGE
SET @MESSAGE_BODY2='The script failed whilst running against the... ' + LTRIM(RTRIM(cast(@@SERVERNAME as VARCHAR(30)))) + ' instance'
SET @MESSAGE_BODY = @MESSAGE_BODY -- + @MESSAGE_BODY3

EXEC msdb.dbo.sp_notify_operator 
@profile_name = @MailProfileName, 
@name=N'Haden Kingsland',
@subject = @MESSAGE_BODY2, 
@body= @MESSAGE_BODY

-- If for some reason this script fails, check for any temporary
-- tables created during the run and drop them for next time.

IF object_id('tempdb..#temp_vm_create') IS NOT NULL
BEGIN
   DROP TABLE #temp_vm_create
END

END CATCH

END