databases_size.txt

DECLARE @DBInfo TABLE
( ServerName VARCHAR(100),
DatabaseName VARCHAR(100),
FileSizeMB INT,
FileType INT,
LogicalFileName sysname,
PhysicalFileName NVARCHAR(520),
Status sysname,
Updateability sysname,
RecoveryMode sysname,
FreeSpaceMB INT,
FreeSpacePct VARCHAR(7),
FreeSpacePages INT,
PollDate datetime)

DECLARE @command VARCHAR(5000)

SELECT @command = 'Use [' + '?' + '] SELECT
@@servername as ServerName,
' + '''' + '?' + '''' + ' AS DatabaseName,
CAST(sysfiles.size/128.0 AS int) AS FileSize,
groupid,
sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName,
CONVERT(sysname,DatabasePropertyEx(''?'',''Status'')) AS Status,
CONVERT(sysname,DatabasePropertyEx(''?'',''Updateability'')) AS Updateability,
CONVERT(sysname,DatabasePropertyEx(''?'',''Recovery'')) AS RecoveryMode,
CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, ' + '''' +
       'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB,
CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name,
' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(sysfiles.size/128.0))
AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + ' AS FreeSpacePct,
GETDATE() as PollDate FROM dbo.sysfiles'
INSERT INTO @DBInfo
   (ServerName,
   DatabaseName,
   FileSizeMB,
   FileType,
   LogicalFileName,
   PhysicalFileName,
   Status,
   Updateability,
   RecoveryMode,
   FreeSpaceMB,
   FreeSpacePct,
   PollDate)
EXEC sp_MSForEachDB @command


/*SELECT
   Databasename, PhysicalFileName, FileSizeMB, FreeSpaceMB
 -- (Sum(FileSizeMB)- SUM(FreeSpaceMB))*100/(Sum(FileSizeMB)) as SpaceUsedPct
  
FROM @DBInfo
--where FileType = 0
--group by DatabaseName, Filetype*/


SELECT
   DatabaseName,
  Sum(FileSizeMB) as TotalSizeMB, SUM(FreeSpaceMB) as FreeSpaceMB, CAST(SUM(FreeSpaceMB) as decimal(10,1))/CAST(Sum(FileSizeMB) as decimal(10,1))
   --PollDate
FROM @DBInfo
where FileType = 0 --(= 0 - transaction log, <> 0 - data files)
group by DatabaseName, Filetype