Track database growth 2012/2014

--
-- Based on http://qa.sqlservercentral.com/scripts/Databases/69634/
--
-- aparentelly doesn't suport DBs with spaces in the name or apostrophes
-- added square brackets in the original code for enhancing
--
-- Adapted and tested for SQL Server 2012 / 2014
--
-- 07/10/2014 - Paulo A. Nascimento
--

USE tempdb
GO

if  ( (select count(*) from sys.objects where name like '#TMP_ServerDrive%') != 0 )
	drop table #TMP_ServerDrive;
if  ( (select count(*) from sys.objects where name like '#TMP_LogSpace%') != 0 )
	drop table #TMP_LogSpace;
if  ( (select count(*) from sys.objects where name like '#TMP_DBFileInfo%') != 0 )
	drop table #TMP_DBFileInfo;
if  ( (select count(*) from sys.objects where name like '#TMP_DataSpace%') != 0 )
	drop table #TMP_DataSpace;
if  ( (select count(*) from sys.objects where name like '#TMP_DB%') != 0 )
	drop table #TMP_DB;

SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @dbname VARCHAR(200),
@sql NVARCHAR(4000)

SET @sql = ''
SET @dbname = ''

CREATE TABLE #TMP_ServerDrive
(
	[DriveName] VARCHAR(5) PRIMARY KEY,
	[FreeDriveSpace] BIGINT
)

INSERT INTO #TMP_ServerDrive
	EXEC master..xp_fixeddrives


CREATE TABLE #TMP_LogSpace
( 
	[DBName] VARCHAR(200) NOT NULL PRIMARY KEY,
	[LogSize] MONEY NOT NULL,
	[LogPercentUsed] MONEY NOT NULL,
	[LogStatus] INT NOT NULL
) 

SELECT @sql = 'DBCC SQLPERF (LOGSPACE) WITH NO_INFOMSGS' 

INSERT INTO #TMP_LogSpace
	EXEC(@sql)

CREATE TABLE #TMP_DBFileInfo 
(
	[dateTimeStamp] datetime default getdate(),
	[DBName] VARCHAR(200),
	[FileLogicalName] VARCHAR(200),
	[FileID] INT NOT NULL,
	[Filename] VARCHAR(250) NOT NULL,
	[Filegroup] VARCHAR(100) NOT NULL,
	[FileCurrentSize] BIGINT NOT NULL,
	[FileMaxSize] VARCHAR(50) NOT NULL,
	[FileGrowth] VARCHAR(50) NOT NULL,
	[FileUsage] VARCHAR(50) NOT NULL,
	[FileGrowthSize] BIGINT NOT NULL
)

CREATE TABLE #TMP_DB ( [DBName] VARCHAR(200) PRIMARY KEY ) 

INSERT INTO #TMP_DB 
-- SELECT DBName = LTRIM(RTRIM(name))
SELECT DBName = name
FROM master.dbo.sysdatabases 
WHERE category IN ('0', '1','16')
AND DATABASEPROPERTYEX(name, 'status') = 'ONLINE' 
ORDER BY name 


CREATE TABLE #TMP_DataSpace
( 
	[DBName] VARCHAR(200) NULL,
	[Fileid] INT NOT NULL,
	[FileGroup] INT NOT NULL,
	[TotalExtents] MONEY NOT NULL,
	[UsedExtents] MONEY NOT NULL,
	[FileLogicalName] sysname NOT NULL,
	[Filename] VARCHAR(1000) NOT NULL
) 

SELECT @dbname = MIN(dbname) FROM #TMP_DB 

WHILE @dbname IS NOT NULL 
BEGIN 
	SET @sql = 'USE [' + @dbname + ']
	INSERT INTO #TMP_DBFileInfo (
	[DBName],
	[FileLogicalName],
	[FileID],
	[Filename],
	[Filegroup],
	[FileCurrentSize],
	[FileMaxSize],
	[FileGrowth],
	[FileUsage],
	[FileGrowthSize])
	SELECT DBName = ''[' + @dbname + ']'',
	FileLogicalName = SF.name, 
	FileID = SF.fileid, 
	Filename = SF.filename, 
	Filegroup = ISNULL(filegroup_name(SF.groupid),''''), 
	FileCurrentSize = (SF.size * 8)/1024, 
	FileMaxSize =CASE SF.maxsize WHEN -1 THEN N''Unlimited'' 
	ELSE CONVERT(VARCHAR(15), (CAST(SF.maxsize AS BIGINT) * 8)/1024) + N'' MB'' END, 
	FileGrowth = (case SF.status & 0x100000 when 0x100000 then 
	convert(varchar(3), SF.growth) + N'' %'' 
	else 
	convert(varchar(15), ((CAST(SF.growth AS BIGINT) * 8)/1024)) + N'' MB'' end), 
	FileUsage = (case WHEN SF.status & 0x40 = 0x40 then ''Log'' else ''Data'' end),
	FileGrowthSize = CASE SF.status & 0x100000 WHEN 0x100000 THEN
	((((CAST(SF.size AS BIGINT) * 8)/1024)* SF.growth)/100) + ((CAST(SF.size AS BIGINT) * 8)/1024)
	ELSE
	((CAST(SF.size AS BIGINT) * 8)/1024) + ((CAST(SF.growth AS BIGINT) * 8)/1024)
	END
	FROM sysfiles SF
	ORDER BY SF.fileid' 
 
	EXEC(@sql) 

	SET @sql = 'USE [' + @dbname + '] DBCC SHOWFILESTATS WITH NO_INFOMSGS'

	INSERT INTO #TMP_DataSpace
	(
		[Fileid],
		[FileGroup],
		[TotalExtents],
		[UsedExtents],
		[FileLogicalName],
		[Filename]
	)
	EXEC (@sql)

	UPDATE #TMP_DataSpace
		SET [DBName] = @dbname
	WHERE ISNULL([DBName],'') = ''
 
	SELECT @dbname = MIN(dbname) FROM #TMP_DB WHERE dbname > @dbname 
END 

-- voltar a limpar os parêntesis rectos, novamente...
UPDATE #TMP_DBFileInfo
	SET DBName=REPLACE(DBName,'[','')

UPDATE #TMP_DBFileInfo
	SET DBName=REPLACE(DBName,']','')

SELECT 
	'Data/Hora' = DFI.dateTimeStamp,
	'Base de dados' = DFI.DBName,
	'Nome lógico do ficheiro' = DFI.FileLogicalName,
	'Nome do ficheiro' = DFI.[Filename],
	'Tamanho ficheiro (MB)' = DFI.FileCurrentSize,
	'Incr. Cresc. ficheiro' = DFI.FileGrowth,
	'Crescimento potencial (MB)' = DFI.FileGrowthSize,
	'Drive' = SD.DriveName,
	'Espaço disponível no drive (MB)' = SD.FreeDriveSpace,
	'Espaço utilizado no ficheiro (MB)' = CAST(ISNULL(((DSP.UsedExtents * 64.00) / 1024), LSP.LogSize *(LSP.LogPercentUsed/100)) AS BIGINT),
	'Espaço vazio ficheiro (MB)' = DFI.FileCurrentSize - CAST(ISNULL(((DSP.UsedExtents * 64.00) / 1024), LSP.LogSize *(LSP.LogPercentUsed/100)) AS BIGINT),
	'Espaço vazio ficheiro (%)' = (CAST((DFI.FileCurrentSize - CAST(ISNULL(((DSP.UsedExtents * 64.00) / 1024), LSP.LogSize *(LSP.LogPercentUsed/100)) AS BIGINT)) AS MONEY) / CAST(CASE WHEN ISNULL(DFI.FileCurrentSize,0) = 0 THEN 1 ELSE DFI.FileCurrentSize END AS MONEY)) * 100
FROM #TMP_DBFileInfo DFI
LEFT OUTER JOIN #TMP_ServerDrive SD
ON LEFT(LTRIM(RTRIM(DFI.[FileName])),1) = LTRIM(RTRIM(SD.DriveName))
LEFT OUTER JOIN #TMP_DataSpace DSP
ON LTRIM(RTRIM(DSP.[Filename])) = LTRIM(RTRIM(DFI.[Filename]))
LEFT OUTER JOIN #TMP_LogSpace LSP
ON LtRIM(RTRIM(LSP.DBName)) = LTRIM(RTRIM(DFI.DBName))
ORDER BY DriveName, DFI.DBName