Automate drive space monitoring for all production servers

CREATE TABLE [dbo].[DBA_All_servers](
[Servername] [sysname] NOT NULL,
[Description] [varchar](50) NULL,
[Status] [int] NULL,
[Version] [varchar](10) NULL
)

CREATE TABLE [dbo].[DBA_All_Server_Space](
[DRIVE] [char](1) NULL,
[FREE_SPACE_IN_MB] [int] NULL,
[SERVER_NAME] [varchar](20) NULL
)

CREATE PROCEDURE [DBO].[USP_DBA_GETSERVERSPACE]

AS BEGIN
TRUNCATE TABLE DBA_ALL_SERVER_SPACE

CREATE TABLE #TEMPSPACE
(
DRIVE VARCHAR(20),
SPACE INT
)

DECLARE @SERVER_NAME VARCHAR(200)

--** PUT LOCAL SERVER FIRST.

INSERT INTO #TEMPSPACE
EXEC XP_FIXEDDRIVES

INSERT INTO DBA_ALL_SERVER_SPACE
SELECT *,@@SERVERNAME AS SERVERNAME FROM #TEMPSPACE

INSERT INTO DBA_ALL_SERVER_SPACE
SELECT NULL,NULL,NULL

PRINT @@SERVERNAME +' COMPLETED.'

DECLARE ALLSERVER CURSOR
FOR
SELECT SERVERNAME FROM DBADATA.DBO.DBA_ALL_SERVERS
OPEN ALLSERVER
FETCH NEXT FROM ALLSERVER INTO @SERVER_NAME

WHILE @@FETCH_STATUS=0
BEGIN
TRUNCATE TABLE #TEMPSPACE

EXEC ('INSERT INTO #TEMPSPACE
EXEC ' + @SERVER_NAME+'.MASTER.DBO.XP_FIXEDDRIVES')

INSERT INTO DBA_ALL_SERVER_SPACE
SELECT *,@SERVER_NAME AS SERVERNAME FROM #TEMPSPACE

/* Optionally insert a blank line. This was added
later for readability of the email */

INSERT INTO DBA_ALL_SERVER_SPACE
SELECT NULL,NULL,NULL

PRINT 'SERVER ' +@SERVER_NAME+' COMPLETED.'

FETCH NEXT FROM ALLSERVER INTO @SERVER_NAME
END
CLOSE ALLSERVER
DEALLOCATE ALLSERVER
DROP TABLE #TEMPSPACE
----------------------------------------------------
-- May be its time to send the report to my DBA

DECLARE @SERVERNAME VARCHAR(200)
DECLARE @DRIVE VARCHAR(200)
DECLARE @SPACE VARCHAR(200)

DECLARE SPACECUR CURSOR FOR
SELECT SERVER_NAME,DRIVE, FREE_SPACE_IN_MB FROM [DBA_ALL_SERVER_SPACE]

OPEN SPACECUR

FETCH NEXT FROM SPACECUR
INTO @SERVERNAME,@DRIVE,@SPACE

DECLARE @BODY1 VARCHAR(2000)
SET @BODY1= 'FOLLOWING ARE SPACE INFO FOR PROD SERVERS: '+ CHAR(13)+
'SERVER'+CHAR(9)+
'DRIVE'+CHAR(9)+
'SPACE'+CHAR(13)

WHILE @@FETCH_STATUS=0
BEGIN
SET @BODY1= @BODY1 +
ISNULL(@SERVERNAME,'')+CHAR(9)+
ISNULL(@DRIVE,'')+CHAR(9)+
ISNULL(@SPACE,'')+CHAR(13)
FETCH NEXT FROM SPACECUR
INTO @SERVERNAME,@DRIVE,@SPACE
END
CLOSE SPACECUR
DEALLOCATE SPACECUR

EXEC MSDB.DBO.SP_SEND_DBMAIL @RECIPIENTS='<YourEmailID>',
@SUBJECT = 'SERVER :NMR\BATS PROD SERVER SPACE INFO ',
@BODY = @BODY1,
@BODY_FORMAT = 'TEXT' ,@PROFILE_NAME='<YourDBMailProfile>';

-------------------------------------------------------

END

USE [MASTER]
CREATE TABLE [DBO].[TEMPSPACE](
[DRIVE] [VARCHAR](20) NULL,
[SPACE] [INT] NULL
) ON [PRIMARY]

CREATE PROC [DBO].[USP_TEMPSPACE_POP]
AS BEGIN
TRUNCATE TABLE TEMPSPACE
INSERT INTO TEMPSPACE
EXEC XP_FIXEDDRIVES
END

WHILE @@FETCH_STATUS=0
BEGIN
TRUNCATE TABLE #TEMPSPACE

EXEC ('EXEC ' + @SERVER_NAME+'.MASTER.DBO.USP_TEMPSPACE_POP')
EXEC ('INSERT INTO #TEMPSPACE SELECT * FROM ' + @SERVER_NAME+'.MASTER.DBO.tempSpace')

INSERT INTO DBA_ALL_SERVER_SPACE
SELECT *,@SERVER_NAME AS SERVERNAME FROM #TEMPSPACE

/* Optionally insert a blank line. This was added
later for readability of the email */

INSERT INTO DBA_ALL_SERVER_SPACE
SELECT NULL,NULL,NULL

PRINT 'SERVER ' +@SERVER_NAME+' COMPLETED.'

FETCH NEXT FROM ALLSERVER INTO @SERVER_NAME
END