BEGIN
CREATE TABLE #LOGSPACE
(DBNAME VARCHAR(100)
,LOGSIZE DECIMAL(30,10)
,LOGSPACE DECIMAL(30,10)
,STATUS INT
)
CREATE TABLE #DRIVESPACE
(DRIVE VARCHAR(2)
,DRIVESPACE BIGINT
)
INSERT INTO #LOGSPACE
EXEC ('DBCC SQLPERF(LOGSPACE)')
INSERT INTO #DRIVESPACE
EXEC ('MASTER.SYS.XP_FIXEDDRIVES')
SELECT F.TYPE_DESC DBFILES
,LEFT(F.PHYSICAL_NAME,1) DRIVE
,F.STATE_DESC DBSTATE
,((F.[SIZE]*8)/1024.00)/1024.00 DBCURRENTSIZE
,ISNULL(((A.RESERVED*8)/1024.00)/1024.00 - ((D.LOGSIZE/1024.00) * (D.LOGSPACE/100.00)),(L.LOGSIZE/1024.00) * (L.LOGSPACE/100.00)) DBRESERVEDSIZE
,CASE WHEN F.IS_PERCENT_GROWTH=0 THEN 'PAGES' WHEN F.IS_PERCENT_GROWTH=1 THEN 'PERCENTAGE' END DBGROWTHTYPE
,F.GROWTH DBGROWTHVALUE
,CASE WHEN F.IS_PERCENT_GROWTH=0 THEN ((F.GROWTH*8)/1024.00)/1024.00 WHEN F.IS_PERCENT_GROWTH=1 THEN (((F.[SIZE]*8)/1024.00)*(F.GROWTH/100.00))/1024.00 END DBGROWTHSIZE
,CASE WHEN F.MAX_SIZE=0 THEN 'FIXED SIZE' WHEN F.MAX_SIZE=-1 THEN 'UNLIMITED' ELSE CONVERT(VARCHAR,((CONVERT(BIGINT,F.MAX_SIZE)*8)/1024.00)/1024.00) END MAX_SIZE
,DS.DRIVESPACE/1024.00 FREESPACEONDRIVE
INTO #SPACEANALYSISREPORT
FROM SYS.DATABASE_FILES F
INNER JOIN #DRIVESPACE DS ON DS.DRIVE = LEFT(F.PHYSICAL_NAME,1)
LEFT OUTER JOIN #LOGSPACE L ON L.DBNAME = DB_NAME() AND F.TYPE_DESC='LOG'
LEFT OUTER JOIN #LOGSPACE D ON D.DBNAME = DB_NAME() AND F.TYPE_DESC='ROWS'
LEFT OUTER JOIN
(SELECT DB_ID() DATABASE_ID, 'ROWS' TYPE_DESC,SUM(TOTAL_PAGES) RESERVED, SUM(USED_PAGES) USED FROM SYS.ALLOCATION_UNITS) A ON A.TYPE_DESC=F.TYPE_DESC
SELECT * FROM #SPACEANALYSISREPORT
DROP TABLE #LOGSPACE
DROP TABLE #DRIVESPACE
DROP TABLE #SPACEANALYSISREPORT
END
