Disk usage for the databases and tempdb.

/*****************************************************************************************************
   Procedure NameSP_PH_DATABASE_USAGE
Created By: Prakash Heda
   Functionality  This procedure will return size of the data files & log files of 
the database and tempdb. This is returned in one resultset so you 
can use this procedure to store fluctuation in database sizes.
   Parameters Usedparameter name    datatype
NANANA
   Tables UsedTable NameOperation Type
SYSOBJECTSSelect
SYSFILEGROUPSSelect 
   Language             T SQL
   Date of Creation     1st May 2002.
   Modifications done   ByDatePurpose
==========================================================================================*/   
USE MASTER
GO
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME = 'SP_PH_DATABASE_USAGE' AND XTYPE = 'P')
DROP PROC SP_PH_DATABASE_USAGE 
GO

CREATE PROCEDURE SP_PH_DATABASE_USAGE AS
SET NOCOUNT ON
IF EXISTS(SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME = '##DATASPACEFORDB' )
DROP TABLE ##DATASPACEFORDB
IF EXISTS(SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME = '##TEMPFORDB' )
DROP TABLE ##TEMPFORDB


CREATE TABLE ##DATASPACEFORDB
 (FILEID NUMERIC,
FILEGRP NUMERIC,
TOTEXTNUMERIC,
USDEXT NUMERIC,
   LFILENMVARCHAR( 100),
   PFILENMVARCHAR( 100)
   )
INSERT ##DATASPACEFORDB
 EXEC( 'DBCC SHOWFILESTATS WITH NO_INFOMSGS')

SELECT FILEID,GROUPNAME,(TOTEXT*64)/1024 AS 'TOTALSPACE_MB',(USDEXT*64)/1024 AS 'SPACEUSED_MB',LFILENM,PFILENM 
INTO ##TEMPFORDB FROM ##DATASPACEFORDB WITH (NOLOCK), SYSFILEGROUPS  WITH (NOLOCK) WHERE FILEGRP = GROUPID
ORDER BY GROUPNAME

INSERT INTO ##TEMPFORDB 
SELECT '50','TOTAL DATA FILE SIZE IN GB',SUM((TOTEXT*64)/1024)/1024 ,SUM((USDEXT*64)/1024)/1024  ,'',''
FROM ##DATASPACEFORDB WITH (NOLOCK), SYSFILEGROUPS  WITH (NOLOCK) WHERE FILEGRP = GROUPID


IF EXISTS(SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME = '##DATASPACEFORTEMPDB' )
DROP TABLE ##DATASPACEFORTEMPDB

IF EXISTS(SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME = '##TEMPFORTEMPDB' )
DROP TABLE ##TEMPFORTEMPDB

CREATE TABLE ##DATASPACEFORTEMPDB
 (FILEID NUMERIC,
FILEGRP NUMERIC,
TOTEXTNUMERIC,
USDEXT NUMERIC,
   LFILENMVARCHAR( 100),
   PFILENMVARCHAR( 100)
   )
INSERT ##DATASPACEFORTEMPDB
 EXEC( 'USE TEMPDB DBCC SHOWFILESTATS WITH NO_INFOMSGS')

SELECT FILEID,GROUPNAME,(TOTEXT*64)/1024 AS 'TOTALSPACE_MB',(USDEXT*64)/1024 AS 'SPACEUSED_MB',LFILENM,PFILENM 
INTO ##TEMPFORTEMPDB FROM ##DATASPACEFORTEMPDB WITH (NOLOCK), SYSFILEGROUPS  WITH (NOLOCK) WHERE FILEGRP = GROUPID
ORDER BY GROUPNAME

INSERT INTO ##TEMPFORTEMPDB 
SELECT '100','TOTAL DATA FILE SIZE IN GB',SUM((TOTEXT*64)/1024)/1024 ,SUM((USDEXT*64)/1024)/1024  ,'',''
FROM ##DATASPACEFORTEMPDB WITH (NOLOCK), SYSFILEGROUPS  WITH (NOLOCK) WHERE FILEGRP = GROUPID

IF EXISTS(SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME = '##TMPLOGSPACE' )
DROP TABLE ##TMPLOGSPACE

CREATE TABLE ##TMPLOGSPACE 
(
DATABASENAME VARCHAR(100),
LOGSIZE VARCHAR(100),
LOGSPACEUSED VARCHAR(100),
STATUS VARCHAR(100)
)

INSERT INTO ##TMPLOGSPACE 
    EXEC ('DBCC SQLPERF ( LOGSPACE ) WITH NO_INFOMSGS')


SELECT '                        ' AS 'DATABASENAME',* INTO ##FINALTABLEFORDB  FROM ##TEMPFORDB WHERE 1=2

INSERT INTO ##FINALTABLEFORDB  
SELECT DB_NAME(),* FROM ##TEMPFORDB 

INSERT INTO ##FINALTABLEFORDB  
 SELECT 'TEMPDB',* FROM ##TEMPFORTEMPDB

INSERT INTO ##FINALTABLEFORDB  
SELECT DATABASENAME,101,'TOTAL LOG SIZE IN GB',CONVERT(NUMERIC(20),LOGSIZE)/1024 , (CONVERT(NUMERIC(20),((CONVERT(NUMERIC(20),LOGSPACEUSED)*LOGSIZE)/100)))/1024,'',''  
FROM ##TMPLOGSPACE WHERE DATABASENAME IN (DB_NAME(),'TEMPDB' )

SELECT UPPER(DATABASENAME),FILEID,GROUPNAME,CONVERT(NUMERIC(20),TOTALSPACE_MB) AS TOTALSPACE_MB,CONVERT(NUMERIC(20),SPACEUSED_MB) AS SPACEUSED_MB ,CONVERT(NUMERIC(20),(TOTALSPACE_MB-SPACEUSED_MB)) AS 'FREESPACE_MB' , LFILENM,PFILENM 
FROM ##FINALTABLEFORDB  ORDER BY 1,2

DROP TABLE ##DATASPACEFORDB, ##TEMPFORDB
DROP TABLE ##DATASPACEFORTEMPDB, ##TEMPFORTEMPDB
DROP TABLE ##TMPLOGSPACE, ##FINALTABLEFORDB

GO