Getting All Disks Free Space information on an instance

you must define linked servers on the instance executing this code if it has to calculate the free disk space information from remote servers.

BEGIN
SET NOCOUNT ON
DECLARE @TOTALCOUNT INT
,@I INT
,@SERVER_NAME NVARCHAR(100)
,@DESCR NVARCHAR(100)
,@STMT NVARCHAR(200)

/* Temporary Table holding Server names */
CREATE TABLE #SERVERS
(PID INT IDENTITY(1,1)
,SERVER_NAME VARCHAR(100)
,DESCR VARCHAR(100))

/* List of All Servers under action */
INSERT INTO #SERVERS(SERVER_NAME,DESCR)
VALUES('SERVER1','FIRST TEST SERVER')

INSERT INTO #SERVERS(SERVER_NAME,DESCR)
VALUES('SERVER2','SECOND TEST SERVER')

/* Total Number of Servers */
SELECT @TOTALCOUNT=COUNT(*) FROM #SERVERS

/* Temporary global table holding Server, Drive and its free space information*/
CREATE TABLE ##MYFREESPACE
(SERVER_NAME VARCHAR(30)
,DRIVE VARCHAR(50)
,FREESPACE BIGINT)

/* Iteration to perform action on each server */
SET @I=1
WHILE (@I<=@TOTALCOUNT)
BEGIN
SELECT @SERVER_NAME = SERVER_NAME,@DESCR=DESCR FROM #SERVERS WHERE PID=@I

/* Try & Catch Block in case of any error */
BEGIN TRY
INSERT INTO ##MYFREESPACE(SERVER_NAME,DRIVE) VALUES(@SERVER_NAME,@DESCR)

SET @STMT = LTRIM(RTRIM(@SERVER_NAME)) + '.MASTER.SYS.XP_FIXEDDRIVES'

INSERT INTO ##MYFREESPACE(DRIVE,FREESPACE)
EXEC SP_EXECUTESQL @STMT
END TRY

BEGIN CATCH
INSERT INTO ##MYFREESPACE(SERVER_NAME,DRIVE) VALUES(@SERVER_NAME,LEFT(CONVERT(VARCHAR,@@ERROR)+'-'+ERROR_MESSAGE(),50))
END CATCH

SET @I = @I + 1

END

/* Send Email by attaching the whole manipulated information */
EXEC MSDB.DBO.SP_SEND_DBMAIL
@PROFILE_NAME = 'TEST ALERTS',
@RECIPIENTS = 'TEST@TEST.COM',
@QUERY = ' SELECT ISNULL(SERVER_NAME,'''') SERVER_NAME, DRIVE,CAST(ISNULL(FREESPACE,0.00)/1024.00 AS DECIMAL(20,3)) FREESPACE_GB FROM ##MYFREESPACE' ,
@SUBJECT = 'FREE DISK SPACE INFORMATION',
@ATTACH_QUERY_RESULT_AS_FILE = 0 ;

/* Drop Temporary Tables */
DROP TABLE #SERVERS
DROP TABLE ##MYFREESPACE

END