Sp_DBGrowth

USE [DB_Growth]

GO

/****** Object: StoredProcedure [dbo].[Weekly_Growth] Script Date: 01/28/2010 16:48:24 ******/

SET
ANSI_NULLS
ON

GO

SET
QUOTED_IDENTIFIER
ON

GO

-- =============================================

-- Author:        <Author,,Name>

-- Create date: <Create Date,,>

-- Description:    <Description,,>

-- =============================================

CREATE
PROCEDURE [dbo].[Weekly_Growth]

    

    

AS

BEGIN

--use master

declare @PageSize varchar(10)

select @PageSize=v.low/1024.0

from
master..spt_values v

where v.number=1 and v.type='E'

 

select name as DatabaseName,
convert(float,null)
as Size

into #tem

From
sys.sysdatabases
where
dbid>4

 

declare @SQL varchar (8000)

set @SQL=''

 

while
exists
(select
*
from #tem where size is
null)

begin

select @SQL='update #tem set size=(select round(sum(size)*'+@PageSize+'/1024,2) From '+quotename(databasename)+'.dbo.sysfiles) where databasename='''+databasename+''''

from #tem

where size is
null

exec (@SQL)

end

 

--select * from #tem order by DatabaseName

 

--Add new column

DECLARE @today_text NVARCHAR(25)

 

--SELECT @today_text = CONVERT(NVARCHAR(20), GETDATE(), 112)

select @today_text =
CONVERT(NVARCHAR(20),
GETDATE(), 6)
+
'@'
+
CONVERT(NVARCHAR(20),DATEPART(HH,
GETDATE()))
+
':'
+
CONVERT(NVARCHAR(20),DATEPART(MINUTE,
GETDATE()))
+
':'
+
CONVERT(NVARCHAR(20),DATEPART(SECOND,
GETDATE()))
+
'(MB)'

 

 

exec ('ALTER TABLE DB_Growth.dbo.DBGrowth ADD "'
+ @today_text +
'" FLOAT'
)

 

 

 

DECLARE DB_Cursor CURSOR

FOR
SELECT databasename, size

FROM #tem

 

 

DECLARE @DB_name NVARCHAR(150)

DECLARE @size FLOAT

DECLARE @today_date NVARCHAR(25)

 

OPEN DB_Cursor

 

FETCH
NEXT
FROM DB_Cursor INTO @DB_name, @size

 

 

--SELECT @today_date = CONVERT(NVARCHAR(20), GETDATE(), 112)

select @today_date =
CONVERT(NVARCHAR(20),
GETDATE(), 6)
+
'@'
+
CONVERT(NVARCHAR(20),DATEPART(HH,
GETDATE()))
+
':'
+
CONVERT(NVARCHAR(20),DATEPART(MINUTE,
GETDATE()))
+
':'
+
CONVERT(NVARCHAR(20),DATEPART(SECOND,
GETDATE()))
+
'(MB)'

 

 

WHILE
@@FETCH_STATUS
= 0

BEGIN

 

-- USE DB_Growth_Test

IF (EXISTS(select [DB_NAME] from DB_Growth.dbo.DBGrowth where
LOWER([DB_NAME])
=
LOWER(@DB_name)
))

BEGIN

--Update new column and put the size for that db

exec ('UPDATE DB_Growth.dbo.DBGrowth SET ['
+ @today_date +
'] = CONVERT(NVARCHAR(10), '
+ @size +
') WHERE LOWER([DB_NAME]) = LOWER('''
+ @DB_name +
''')')

 

 

END

ELSE

 

BEGIN

----Create another row and put the size in the newly added column

exec ('INSERT DB_Growth.dbo.DBGrowth(DB_NAME, "'
+ @today_date +
'") VALUES( '''
+ @DB_name +
''', CONVERT(NVARCHAR(10), '
+ @size +
'))')

 

END

 

FETCH
NEXT
FROM DB_Cursor into @DB_name, @size

END

CLOSE DB_Cursor;

DEALLOCATE DB_Cursor;

 

 

drop
table #tem

 

 

 

END