-----code:start
--------------------------------------------------------------------------------------------
USE MASTER
GO
CREATE TABLE #TMPFIXEDDRIVES
(
DRIVE CHAR(1),
MBFREE INT
)
INSERT INTO #TMPFIXEDDRIVES
EXEC xp_FIXEDDRIVES
CREATE TABLE #TMPSPACEUSED
(
DBNAME VARCHAR(50),
FILENME VARCHAR(50),
SPACEUSED FLOAT
)
INSERT INTO #TMPSPACEUSED
EXEC
( 'sp_msforeachdb''use ?;
Select ''''?''''
DBName, Name FileNme, fileproperty(Name,''''SpaceUsed'''') SpaceUsed
from sysfiles'''
)
if EXISTS
(
SELECT * FROM INFORMATION_SCHEMA.tables
WHERE table_name ='Temp_Free_Space_Of_Database'
)
DROP TABLE Temp_Free_Space_Of_Database
SELECT * INTO Temp_Free_Space_Of_Database
FROM
(
SELECT C.DRIVE,
CASE
WHEN (C.MBFREE) > 1000
THEN CAST(
CAST(((C.MBFREE) / 1024.0) AS DECIMAL(18,2))
AS VARCHAR(20)) + ' GB'
ELSE CAST(
CAST((C.MBFREE) AS DECIMAL(18,2))
AS VARCHAR(20)) + ' MB'
END AS DISKSPACEFREE,
A.NAME AS DATABASENAME,
B.NAME AS FILENAME,
CASE B.TYPE
WHEN 0 THEN 'DATA'
ELSE TYPE_DESC
END AS FILETYPE,
CASE
WHEN (B.SIZE * 8 / 1024.0) > 1000
THEN CAST(
CAST(((B.SIZE * 8 / 1024) / 1024.0) AS DECIMAL(18,2))
AS VARCHAR(20)) + ' GB'
ELSE CAST(
CAST((B.SIZE * 8 / 1024.0) AS DECIMAL(18,2))
AS VARCHAR(20)) + ' MB'
END AS FILESIZE,
CAST((B.SIZE * 8 / 1024.0) - (D.SPACEUSED / 128.0) AS DECIMAL(15,2)) SPACEFREE,
B.PHYSICAL_NAME
FROM SYS.DATABASES A
JOIN SYS.MASTER_FILES B
ON A.DATABASE_ID = B.DATABASE_ID
JOIN #TMPFIXEDDRIVES C
ON LEFT(B.PHYSICAL_NAME,1) = C.DRIVE
JOIN #TMPSPACEUSED D
ON A.NAME = D.DBNAME
AND B.NAME = D.FILENME
) A
DROP TABLE #TMPFIXEDDRIVES
DROP TABLE #TMPSPACEUSED
SELECT * FROM Temp_Free_Space_Of_Database
--------------------------------------------------------------------------------------------
-----code:end
--------------------------------------------------------------------------------------------
Result Messages:
Source:
http://www.mssqltips.com/sqlservertip/1510/script-to-determine-free-space-to-support-shrinking-sql-server-database-files/