Script to find out free space for each file in every database on a particular SQL Server Instance

SET NOCOUNT ON IF OBJECT_ID('tempdb..#DBINFO') IS NOT NULLBEGINDROP TABLE #DBINFOEND CREATE TABLE #DBINFO (dbname sysname,lname sysname,TYPE varchar(20),[size] decimal(9, 2) NULL ,[used]
)decimal(9, 2) NULL IF OBJECT_ID('tempdb..#LOGINFO') IS NOT NULLBEGINDROP TABLE #LOGINFOEND CREATE TABLE #LOGINFO(DBName sysname,LogSize real,LogSpaceUsed real,Status int) IF OBJECT_ID('tempdb..#TEMPSTATS') IS NOT NULLBEGINDROP TABLE #TEMPSTATSEND CREATE TABLE #TEMPSTATS(fileid int,filegroup int,totalextents int,usedextents int,name varchar(1024),
)filename varchar(1024) DECLARE @dbname sysname,@sql varchar(8000) IF OBJECT_ID('tempdb..#temp_db') IS NOT NULLBEGINDROP TABLE #temp_dbEND SELECT
name INTO #temp_dbFROM master.dbo.sysdatabases

WHERE DATABASEPROPERTY(name,'IsOffline') = 0AND has_dbaccess(name) = 1ORDER BY name WHILE (1 = 1)BEGIN



SET @dbname = NULL SELECT TOP 1 @dbname = nameFROM #temp_dbORDER BY name IF @dbname IS NULL

TRUNCATE TABLE #TEMPSTATS
INSERT INTO #TEMPSTATS
EXECUTE(''DBCC SHOWFILESTATS'')
INSERT INTO #DBINFO (DBNAME, LNAME, TYPE, [SIZE], [USED])
SELECT db_name(), NAME, ''Data''
, totalextents * 64.0 / 1024.0
, usedextents * 64.0 / 1024.0
FROM #TEMPSTATS
INSERT INTO #DBINFO (DBNAME, LNAME, TYPE, [SIZE], [USED])
SELECT db_name(), name, ''Log'', null, null
FROM sysfiles
WHERE status & 0x40 = 0x40'

GOTO _NEXT SET @sql = ' USE ' + @dbname + ' EXEC(@sql) DELETE FROM #temp_db WHERE name = @dbnameEND _NEXT: INSERT
INTO #LOGINFOEXECUTE ('DBCC SQLPERF(LOGSPACE)') UPDATE

#DBINFOSET SIZE = B.LogSize, USED = LogSize * LogSpaceUsed / 100FROM #DBINFO AINNER
JOIN #LOGINFO BON (A.DBNAME = B.DBNAME)AND(A.TYPE = 'LOG') SELECT dbname AS [database name],lname AS [LOGICAL FILE NAME],TYPE,[size] AS [space allocated (MB)],used AS[space used (MB)],[size] - used AS [free space (MB)],cast(used/[size]*100 AS numeric(9,2)) AS [space used %],cast(100-(used/[size]*100) AS numeric(9,2)) AS [free space %]FROM #DBINFOORDER BY dbname, TYPE DROP TABLE #DBINFODROP TABLE #temp_dbDROP TABLE #TEMPSTATSDROP TABLE #LOGINFO SET




Comment
===========
The script would return the following columns:

database name
logical file name
TYPE (data or log)
space allocated (MB)
space used (MB)
free space (MB)
space used %
free space %


--Abhishek Soni NOCOUNT OFF