--==============================================================
--Script Author:  Timothy Ford aka SQLAgentMan
-- http://thesqlagentman.com
-- Can be used for personal use; credit must be given to author
-- Code can not be used for commercial use, repackaged, resold without permission
--==============================================================

--==============================================================
--CREATE DATABASE iDBA IF IT DOES NOT ALREADY EXIST:
--==============================================================
/*
IF NOT EXISTS (SELECT name FROM master..sysdatabases WHERE name = 'iDBA')
	BEGIN	
		CREATE DATABASE iDBA;
		ALTER DATABASE [iDBA] SET AUTO_SHRINK OFF;
		ALTER DATABASE [iDBA] SET RECOVERY SIMPLE WITH NO_WAIT;
		ALTER DATABASE [iDBA] MODIFY FILE (NAME = N'iDBA', MAXSIZE = 1000MB , FILEGROWTH = 100MB);
		ALTER DATABASE [iDBA] MODIFY FILE (NAME = N'iDBA_log', MAXSIZE = 200MB , FILEGROWTH = 100MB);
	END

*/


--====================================================================
--CREATE THE STORED PROCEDURE AS USED FOR LOADING DBA REPOSITORY NIGHTLY	
--====================================================================
USE [iDBA];

CREATE PROCEDURE [dbo].[usp_database_file_snapshot] AS 
DECLARE @SQL VARCHAR(5000) 
DECLARE @version smallint


--DETERMINE IF THE INSTANCE IS SQL 2005 OR LATER SO CATALOG VIEWS CAN BE USED
SELECT @version = CONVERT(smallint, LEFT(CONVERT(varchar(20),SERVERPROPERTY('ProductVersion')),CHARINDEX('.',CONVERT(varchar(20),SERVERPROPERTY('ProductVersion')))-1))

IF EXISTS (SELECT NAME FROM tempdb..sysobjects WHERE NAME = 'RESULTS_FILE_HISTORY')  
   BEGIN  
       DROP TABLE [tempdb].[dbo].[RESULTS_FILE_HISTORY]  
   END 
    
CREATE TABLE [tempdb].[dbo].[RESULTS_FILE_HISTORY] ([Server] nvarchar(128), [DatabaseName] sysname, [Name] sysname, [Filename] NVARCHAR(260),  
   [FileType] varchar(4), [Size_In_Mb] int, [Available_Space_In_Mb] int, [Growth_Increments] int, [Growth_Units] varchar(2), 
   [Max_File_Size_In_Mb] int) 

--IF THIS IS A SQL 2000 OR EARLIER INSTANCE YOU MUST USE THE SYSTEM TABLES
IF @version < 9
	BEGIN
		SELECT @SQL =  
		'USE [?] INSERT INTO [tempdb].[dbo].[RESULTS_FILE_HISTORY]([Server], [DatabaseName], [Name], [Filename],  
		[FileType], [Size_In_Mb], [Available_Space_In_Mb], [Growth_Increments], 
		[Growth_Units], [Max_File_Size_In_Mb])  
		SELECT @@servername, DB_NAME(), 
		[name] AS [Name],  
		[filename] AS [Filename],  
		
		[FileType] =  
		CASE (status & 0x40) 
		WHEN 0 THEN ''Data'''  
		+ 
				   'ELSE ''Log''' 
		+ 
			   'END, 
		
		[Size_In_Mb] = 
		CASE ceiling([size]/128)  
		WHEN 0 THEN 1 
		ELSE ceiling([size]/128) 
		END, 
		[Available_Space_In_Mb] =  
		CASE ceiling([size]/128) 
		WHEN 0 THEN (1 - CAST(FILEPROPERTY([name], ''SpaceUsed''' + ') as int) /128) 
		ELSE (([size]/128) - CAST(FILEPROPERTY([name], ''SpaceUsed''' + ') as int) /128) 
		END, 
		
		[Growth_Increments] = 
		CASE (status & 0x100000)  
		WHEN 0 THEN CAST(CEILING([growth]/1024*8) AS int)'  
		+ 
				   'ELSE [growth]' 
		+ 
			   'END,
		
		[Growth_Units]  =  
		CASE (status & 0x100000)  
		WHEN 0 THEN ''Mb'''  
		+ 
				   'ELSE ''%''' 
		+ 
			   'END, 
		[Max_File_Size_In_Mb] =  
		CASE [maxsize] 
		WHEN -1 THEN NULL 
		WHEN 268435456 THEN NULL 
		ELSE [maxsize]/1024*8 
		END 
		FROM dbo.sysfiles
		ORDER BY [fileid]' 
	END
ELSE	--IF THIS IS A SQL 2005 OR LATER INSTANCE YOU WILL USE THE CATALOG VIEWS
	BEGIN
		SELECT @SQL =  
			'USE [?] INSERT INTO [tempdb].[dbo].[RESULTS_FILE_HISTORY]([Server], [DatabaseName], [Name], [Filename],  
			[FileType], [Size_In_Mb], [Available_Space_In_Mb], [Growth_Increments], 
			[Growth_Units], [Max_File_Size_In_Mb])  
			SELECT @@servername, DB_NAME(), 
			[name] AS [Name],  
			physical_name AS [Filename],  
			[FileType] =  
			CASE type 
			WHEN 0 THEN ''Data'''  
			+ 
					   'WHEN 1 THEN ''Log''' 
			+ 
				   'END, 
			[Size_In_Mb] = 
			CASE ceiling([size]/128)  
			WHEN 0 THEN 1 
			ELSE ceiling([size]/128) 
			END, 
			[Available_Space_In_Mb] =  
			CASE ceiling([size]/128) 
			WHEN 0 THEN (1 - CAST(FILEPROPERTY([name], ''SpaceUsed''' + ') as int) /128) 
			ELSE (([size]/128) - CAST(FILEPROPERTY([name], ''SpaceUsed''' + ') as int) /128) 
			END, 
			
			[Growth_Increments] = 
			CASE [is_percent_growth]  
			WHEN 0 THEN [growth]'  
			+ 
					   'ELSE CAST(CEILING([growth]/1024*8) AS int)' 
			+ 
				   'END,
			[Growth_Units]  =  
			CASE [is_percent_growth]  
			WHEN 1 THEN''%''' 
			+ 
					   'ELSE ''Mb''' 
			+ 
				   'END, 
			[Max_File_Size_In_Mb] =  
			CASE [max_size] 
			WHEN -1 THEN NULL 
			WHEN 268435456 THEN NULL 
			ELSE [max_size]/1024*8 
			END 
			FROM sys.database_files 
			ORDER BY [FileType], [file_id]' 
	END 

--Run the command against each database 
EXEC sp_MSforeachdb @SQL 

--You can uncomment the following statement in order to see the results returned in SSMS:
/*
SELECT [Server], [DatabaseName], [Name], [Filename], 
	[FileType], [Size_In_Mb], [Available_Space_In_Mb], 
	CEILING(CAST([Available_Space_In_Mb] AS decimal(10,1)) / [Size_In_Mb]*100) AS [Free Space %], 
	[Growth_Increments], [Growth_Units], [Max_File_Size_In_Mb]  
FROM [tempdb].[dbo].[RESULTS_FILE_HISTORY];  
*/
 
GO