Untitled

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