/*******************************************************************\
*               Copyright © Pro-DBA.com 2006, all rights reserved   *
*                                                                   *
* Name     :    ShowDBSizs.sql                                      *
*                                                                   *
* Purpose  :    Lists file sizes of all data and log files          *
*                                                                   *
* Tested on:    SQL Server 7.0, SQL Server 2000, SQL Server 2005    *
*                                                                   *
*                                                                   *
\*******************************************************************/

SET ANSI_WARNINGS OFF
SET NOCOUNT ON

CREATE TABLE #DBFileStats
(
[DBName] VARCHAR(128),
[DBID] INT,
[LogicalName] SYSNAME,
[FileID] INT,
[FileName] VARCHAR(255),
[FileGroup] VARCHAR(100),
[Size] VARCHAR(25),
[MaxSize] VARCHAR(25),
[Growth] VARCHAR(25),
[Usage] VARCHAR(25),
[TotalMB] DECIMAL(19,2),
[UsedMB] DECIMAL(19,2),
[FreeMB] AS CAST([TotalMB]-[UsedMB] AS DECIMAL(19,2)),
[Free%] AS CAST(([TotalMB]-[UsedMB])/[TotalMB]*100.00 AS DECIMAL(4,2))
)

IF exists(SELECT * FROM tempdb..sysobjects WHERE [id] = OBJECT_ID('tempdb..#Stats'))
DROP TABLE #Stats

CREATE TABLE #Stats
(
[FileId] INT,
[FileGroup] INT,
[TotalExtents] DECIMAL(19,4),
[UsedExtents] DECIMAL(19,4),
[TotalMB] AS [TotalExtents]*64/1024,
[UsedMB] AS [UsedExtents]*64/1024,
[Name] SYSNAME,
[FileName] VARCHAR(255)
)

IF exists(SELECT * FROM tempdb..sysobjects WHERE [id] = OBJECT_ID('tempdb..#DBLogSpace'))
DROP TABLE #DBLogSpace

CREATE TABLE #DBLogSpace
(
[DBName] SYSNAME,
[LogSizeMB] DECIMAL(19,4),
[LogSpaceUsed%] DECIMAL(19,4),
[Status] INT
)

--Gather Stats for all databases
exec sp_msforeachdb
@command1 = 'use [?];INSERT into #Stats([FileID], [FileGroup], [TotalExtents], [UsedExtents], [Name], [FileName]) exec (''dbcc showfilestats with no_infomsgs'')',
@command2 = 'use [?];INSERT into #DBFileStats([LogicalName], [FileID], [FileName], [FileGroup], [Size], [MaxSize], [Growth], [Usage]) exec sp_helpfile;UPDATE #DBFileStats SET [DBName] = ''?'' WHERE DBName is Null;UPDATE #DBFileStats SET [DBID] = DB_ID(''?'') WHERE [DBID] is null'

--Gather log usage stats
INSERT #DBLogSpace EXEC ('dbcc sqlperf(logspace) with no_infomsgs')

--Merge file stats with database info
UPDATE #DBFileStats SET TotalMB = fs.TotalMB, UsedMB = fs.UsedMB
FROM #DBFileStats hf INNER join #Stats fs ON hf.[FileName] = fs.[FileName]
WHERE hf.[TotalMB] is null

UPDATE #DBFileStats SET TotalMB = ls.LogSizeMB, UsedMB = ls.LogSizeMB*ls.[LogSpaceUsed%]/100
FROM #DBLogSpace ls INNER join #DBFileStats hf ON hf.[DBName] = ls.[DBName]
WHERE hf.[FileGroup] is null

SELECT [DBName], [LogicalName], [TotalMB], [FreeMB], [Free%], [Growth], [Usage] 
FROM #DBFileStats
ORDER BY [DBName] ASC, [USAGE] ASC, [LogicalName] ASC

--Clean up
DROP TABLE #Stats
DROP TABLE #DBLogSpace
DROP TABLE #DBFileStats