...Here is the 2000 Version..of the code....
--PART 1
If exists (Select name from sysobjects where name = 'DBGrowthRate' and Type = 'U')
Drop Table dbo.DBGrowthRate
Create Table dbo.DBGrowthRate (DBGrowthID int identity(1,1), DBName varchar(100), DBID int,
NumPages int, OrigSize decimal(10,2), CurSize decimal(10,2), GrowthAmt decimal(10,2),
MetricDate datetime)
Select sd.name as DBName, mf.name as FileName, mf.dbid, fileid, size
into #TempDBSize
from master.dbo.sysdatabases sd
join master.dbo.sysaltfiles mf
on sd.dbid = mf.dbid
Order by mf.dbid, sd.name
Insert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize, CurSize, GrowthAmt, MetricDate)
(Select tds.DBName, tds.dbid, Sum(tds.Size) as NumPages,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as OrigSize,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as CurSize,
0 as GrowthAmt, GetDate() as MetricDate
from #TempDBSize tds
where tds.dbid not in (Select Distinct DBID from DBGrowthRate
where DBName = tds.dbid)
Group by tds.dbid, tds.DBName)
Drop table #TempDBSize
Select *
from DBGrowthRate
--Above creates initial table and checks initial data
--PART 2
--Below is the code run weekly to check the growth.
Select sd.name as DBName, mf.name as FileName, mf.dbid, fileid, size
into #TempDBSize2
from master.dbo.sysdatabases sd
join master.dbo.sysaltfiles mf
on sd.dbid = mf.dbid
Order by mf.dbid, sd.name
If Exists (Select Distinct DBName from #TempDBSize2
where DBName in (Select Distinct DBName from DBGrowthRate))
and GetDate() > (Select Distinct Max(MetricDate) as MetricDate from DBGrowthRate)
Begin
Insert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize, CurSize, GrowthAmt, MetricDate)
(Select tds.DBName, tds.dbid, Sum(tds.Size) as NumPages,
dgr.CurSize as OrigSize,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as CurSize,
((Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024))) - dgr.CurSize )as GrowthAmt, GetDate() as MetricDate
from #TempDBSize2 tds
join DBGrowthRate dgr
on tds.dbid = dgr.DBID
Where DBGrowthID = (Select Distinct Max(DBGrowthID) from DBGrowthRate
where DBID = dgr.DBID)
Group by tds.dbid, tds.DBName,dgr.CurSize, dgr.OrigSize)
End
--Select *
--from DBGrowthRate
----Verifies values were entered
Drop table #TempDBSize2