sp_sizing

USE [master]
GO

CREATE PROCEDURE [dbo].[sp_sizing] @granularity 
	varchar(1) = NULL, @database_name sysname = NULL AS

/*-------------------------------------------------------------
dbo.sp_sizing Stored Procedure 
Created by Tim Ford, www.sqlcruise.com, www.thesqlagentman.com
Use freely but review code before executing.
Code downloaded from internet so execute at your own risk.
-------------------------------------------------------------*/

DECLARE @sql_command VARCHAR(5000) 

CREATE TABLE #Results ([server] NVARCHAR(128), [database_name] NVARCHAR(128), [file_name] NVARCHAR(128), [physical_name] NVARCHAR(260),  
   [file_type] VARCHAR(4), [total_size_mb] INT, [available_space_mb] INT, [growth_units] VARCHAR(15), [max_file_size_mb] INT) 

SELECT @sql_command =  
'USE [?] INSERT INTO #Results([server], [database_name], [file_name], [physical_name],  
[file_type], [total_size_mb], [available_space_mb],  
[growth_units], [max_file_size_mb])  
SELECT CONVERT(nvarchar(128), SERVERPROPERTY(''Servername'')), DB_NAME(), 
[name] AS [file_name],  
physical_name AS [physical_name],  
[file_type] =  
CASE type 
WHEN 0 THEN ''Data'''  
+ 
       'WHEN 1 THEN ''Log''' 
+ 
   'END, 
[total_size_mb] = 
CASE ceiling([size]/128)  
WHEN 0 THEN 1 
ELSE ceiling([size]/128) 
END, 
[available_space_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_units]  =  
CASE [is_percent_growth]  
WHEN 1 THEN CAST([growth] AS varchar(20)) + ''%''' 
+ 
       'ELSE CAST([growth]/1024*8 AS varchar(20)) + ''Mb''' 
+ 
   'END, 
[max_file_size_mb] =  
CASE [max_size] 
WHEN -1 THEN NULL 
WHEN 268435456 THEN NULL 
ELSE [max_size]/1024*8 
END 
FROM sys.database_files WITH (NOLOCK)
ORDER BY [file_type], [file_id]' 

--Print the command to be issued against all databases 
--PRINT @sql_command 

--========================================
--RUN COMMAND AGAINST EACH DATABASE
--========================================
EXEC sp_MSforeachdb @sql_command 


--=================================
--RETURN THE RESULTS

--If @database_name is NULL: 
--=================================
IF @database_name IS NULL 
   BEGIN 
   IF @granularity= 'd' /* Database Scope */
       BEGIN 
       SELECT  
           T.[server], T.[database_name], 
           T.[total_size_mb] AS [db_size_mb], 
           T.[available_space_mb] AS [db_free_mb], 
           T.[used_space_mb] AS [db_used_mb], 
           D.[total_size_mb] AS [data_size_mb], 
           D.[available_space_mb] AS [data_free_mb], 
           D.[used_space_mb] AS [data_used_mb], 
           CEILING(CAST(D.[available_space_mb] AS decimal(10,1)) / D.[total_size_mb]*100) AS [data_free_pct],
           L.[total_size_mb] AS [log_size_mb], 
           L.[available_space_mb] AS [log_free_mb], 
           L.[used_space_mb] AS [log_used_mb], 
           CEILING(CAST(L.[available_space_mb] AS decimal(10,1)) / L.[total_size_mb]*100) AS [log_free_pct] 
       FROM  
           ( 
	   SELECT [server], [database_name], 
		   SUM([total_size_mb]) AS [total_size_mb], 
		   SUM([available_space_mb]) AS [available_space_mb], 
		   SUM([total_size_mb]-[available_space_mb]) AS [used_space_mb]  
	   FROM #Results 
	   GROUP BY [server], [database_name] 
           ) AS T 
           INNER JOIN  
	   ( 
		   SELECT [server], 
				[database_name], 
				SUM([total_size_mb]) AS [total_size_mb], 
				SUM([available_space_mb]) AS [available_space_mb], 
				SUM([total_size_mb]-[available_space_mb]) AS [used_space_mb]  
		   FROM #Results 
		   WHERE #Results.[file_type] = 'Data' 
		   GROUP BY [server], [database_name] 
	   ) AS D ON T.[database_name] = D.[database_name] 
           INNER JOIN 
	   ( 
		   SELECT [server],
				[database_name], 
				SUM([total_size_mb]) AS [total_size_mb], 
				SUM([available_space_mb]) AS [available_space_mb], 
				SUM([total_size_mb]-[available_space_mb]) AS [used_space_mb]  
		   FROM #Results 
		   WHERE #Results.[file_type] = 'Log' 
		   GROUP BY [server], [database_name] 
	   ) AS L ON T.[database_name] = L.[database_name] 
       ORDER BY D.[database_name] 
       END 
	ELSE /* File Scope */
		BEGIN 
			SELECT [server], 
				[database_name], 
				[file_name], 
				[physical_name], 
				[file_type], 
				[total_size_mb] AS [db_size_mb], 
				[available_space_mb] AS [db_free_mb], 
				CEILING(CAST([available_space_mb] AS DECIMAL(10,1)) / [total_size_mb]*100) AS [free_space_pct], 
				[growth_units], 
				[max_file_size_mb] /* AS [Grow Max Size (Mb)]  */
			FROM #Results  
			ORDER BY database_name, file_type, [file_name]
		END 
	END 

--=================================
--RETURN THE RESULTS FOR A DATABASE

--If @database_name is provided: 
--=================================
ELSE 
   BEGIN 
   IF @granularity= 'd' /* Database Scope */
       BEGIN 
       SELECT 
	T.[server],
					T.[database_name], 
	T.[total_size_mb] AS [db_size_mb], 
	T.[available_space_mb] AS [db_free_mb], 
	T.[used_space_mb] AS [db_used_mb], 
	D.[total_size_mb] AS [data_size_mb], 
	D.[available_space_mb] AS [data_free_mb], 
	D.[used_space_mb] AS [data_used_mb], 
	CEILING(CAST(D.[available_space_mb] AS DECIMAL(10,1)) / D.[total_size_mb]*100) AS [data_free_pct], 
	L.[total_size_mb] AS [log_size_mb], 
	L.[available_space_mb] AS [log_free_mb], 
	L.[used_space_mb] AS [log_used_mb], 
	CEILING(CAST(L.[available_space_mb] AS DECIMAL(10,1)) / L.[total_size_mb]*100) AS [log_free_pct] 
       FROM  
           ( 
           SELECT [server], [database_name], 
           SUM([total_size_mb]) AS [total_size_mb], 
           SUM([available_space_mb]) AS [available_space_mb], 
           SUM([total_size_mb]-[available_space_mb]) AS [used_space_mb]  
           FROM #Results 
           WHERE [database_name] = @database_name 
           GROUP BY [server], [database_name] 
           ) AS T 
           INNER JOIN  
	   ( 
		   SELECT [server], [database_name], 
			   SUM([total_size_mb]) AS [total_size_mb], 
			   SUM([available_space_mb]) AS [available_space_mb], 
			   SUM([total_size_mb]-[available_space_mb]) AS [used_space_mb]  
		   FROM #Results 
		   WHERE #Results.[file_type] = 'Data' 
			   AND [database_name] = @database_name 
		   GROUP BY [server], [database_name] 
	   ) AS D ON T.[database_name] = D.[database_name] 
           INNER JOIN 
	   ( 
		   SELECT [server], [database_name], 
			   SUM([total_size_mb]) AS [total_size_mb], 
			   SUM([available_space_mb]) AS [available_space_mb], 
			   SUM([total_size_mb]-[available_space_mb]) AS [used_space_mb]  
		   FROM #Results 
		   WHERE #Results.[file_type] = 'Log' 
			   AND [database_name] = @database_name 
		   GROUP BY [server], [database_name] 
	   ) AS L ON T.[database_name] = L.[database_name] 
       ORDER BY D.[database_name] 
       END 
   ELSE /* File Scope */
       BEGIN 
       SELECT [server],
					[database_name], 
	[file_name], 
	[physical_name], 
	[file_type], 
	[total_size_mb] AS [db_size_mb], 
	[available_space_mb] AS [db_free_mb], 
	CEILING(CAST([available_space_mb] AS DECIMAL(10,1)) / [total_size_mb]*100) AS [free_space_pct], 
	[growth_units], 
	[max_file_size_mb] /* AS [Grow Max Size (Mb)]  */
       FROM #Results  
       WHERE [database_name] = @database_name 
	 ORDER BY file_type, [file_name]
       END 
   END 
GO