The below script would provide the Actual DB file usage size rather than DB size (Used + Free) should work for SQL 2008 and upwards
Borrowed the “dbo.sp_foreachdb” script from Aaron Bertrand (making-a-more-reliable-and-flexible-spmsforeachdb/) to list out all databases in a SQL server.
Step1: Create the below stored procedure.
USE [master];
GO
CREATE PROCEDURE dbo.sp_foreachdb
@command NVARCHAR(MAX),
@replace_character NCHAR(1) = N’?’,
@print_dbname BIT = 0,
@print_command_only BIT = 0,
@suppress_quotename BIT = 0,
@system_only BIT = NULL,
@user_only BIT = NULL,
@name_pattern NVARCHAR(300) = N’%’,
@database_list NVARCHAR(MAX) = NULL,
@recovery_model_desc NVARCHAR(120) = NULL,
@compatibility_level TINYINT = NULL,
@state_desc NVARCHAR(120) = N’ONLINE’,
@is_read_only BIT = 0,
@is_auto_close_on BIT = NULL,
@is_auto_shrink_on BIT = NULL,
@is_broker_enabled BIT = NULL
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@sql NVARCHAR(MAX),
@dblist NVARCHAR(MAX),
@db NVARCHAR(300),
@i INT;
IF @database_list > N”
BEGIN
;WITH n(n) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY s1.name) – 1
FROM sys.objects AS s1
CROSS JOIN sys.objects AS s2
)
SELECT @dblist = REPLACE(REPLACE(REPLACE(x,”,’,’),
”,”),”,”)
FROM
(
SELECT DISTINCT x = ‘N”’ + LTRIM(RTRIM(SUBSTRING(
@database_list, n,
CHARINDEX(‘,’, @database_list + ‘,’, n) – n))) + ””
FROM n WHERE n <= LEN(@database_list)
AND SUBSTRING(‘,’ + @database_list, n, 1) = ‘,’
FOR XML PATH(”)
) AS y(x);
END
CREATE TABLE #x(db NVARCHAR(300));
SET @sql = N’SELECT name FROM sys.databases WHERE 1=1′
+ CASE WHEN @system_only = 1 THEN
‘ AND database_id IN (1,2,3,4)’
ELSE ” END
+ CASE WHEN @user_only = 1 THEN
‘ AND database_id NOT IN (1,2,3,4)’
ELSE ” END
+ CASE WHEN @name_pattern <> N’%’ THEN
‘ AND name LIKE N”%’ + REPLACE(@name_pattern, ””, ”””) + ‘%”’
ELSE ” END
+ CASE WHEN @dblist IS NOT NULL THEN
‘ AND name IN (‘ + @dblist + ‘)’
ELSE ” END
+ CASE WHEN @recovery_model_desc IS NOT NULL THEN
‘ AND recovery_model_desc = N”’ + @recovery_model_desc + ””
ELSE ” END
+ CASE WHEN @compatibility_level IS NOT NULL THEN
‘ AND compatibility_level = ‘ + RTRIM(@compatibility_level)
ELSE ” END
+ CASE WHEN @state_desc IS NOT NULL THEN
‘ AND state_desc = N”’ + @state_desc + ””
ELSE ” END
+ CASE WHEN @is_read_only IS NOT NULL THEN
‘ AND is_read_only = ‘ + RTRIM(@is_read_only)
ELSE ” END
+ CASE WHEN @is_auto_close_on IS NOT NULL THEN
‘ AND is_auto_close_on = ‘ + RTRIM(@is_auto_close_on)
ELSE ” END
+ CASE WHEN @is_auto_shrink_on IS NOT NULL THEN
‘ AND is_auto_shrink_on = ‘ + RTRIM(@is_auto_shrink_on)
ELSE ” END
+ CASE WHEN @is_broker_enabled IS NOT NULL THEN
‘ AND is_broker_enabled = ‘ + RTRIM(@is_broker_enabled)
ELSE ” END;
INSERT #x EXEC sp_executesql @sql;
DECLARE c CURSOR
LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR SELECT CASE WHEN @suppress_quotename = 1 THEN
db
ELSE
QUOTENAME(db)
END
FROM #x ORDER BY db;
OPEN c;
FETCH NEXT FROM c INTO @db;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = REPLACE(@command, @replace_character, @db);
IF @print_command_only = 1
BEGIN
PRINT ‘/* For ‘ + @db + ‘: */’
+ CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
+ @sql
+ CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10);
END
ELSE
BEGIN
IF @print_dbname = 1
BEGIN
PRINT ‘/* ‘ + @db + ‘ */’;
END
EXEC sp_executesql @sql;
END
FETCH NEXT FROM c INTO @db;
END
CLOSE c;
DEALLOCATE c;
END
Step2: Run the below on the Server to generate the report, it can be modified as need by adding more columns or removing them.
Create Table ##temp
(
DatabaseName sysname,
DB_File_Name sysname,
[Size-Mb] decimal (18,2),
[Used-Mb] decimal (18,2),
[Maximum_Size-Mb] decimal (18,2),
[Growth] varchar(20),
[GroupName] sysname,
[%Free] int
)
Exec sp_foreachdb’
Use ?;
Insert Into ##temp (DatabaseName, DB_File_Name, [Size-Mb], [Used-Mb],[Maximum_Size-Mb],[Growth],[GroupName],[%Free])
Select DB_NAME() AS [DatabaseName], Name,
cast(size/128.0 as numeric(10,1)) as [size-mb],
cast(FILEPROPERTY(name, ”spaceused”)/128.0 as numeric(10,1)) as [used-mb],
maxsize [maximum size-mb],
cast(growth*8/1024 as varchar(11))+
case when status&0x100000>0 then ”%” else ”” end [growth],
isnull(filegroup_name(groupid),”log”) [groupname],
cast(100*(size/128-(FILEPROPERTY(name,”spaceused”)/128))/(size/128.0) as int) [% free]
from sysfiles
‘
Select * From ##temp
drop table ##temp
