------------------------------------------------------------------------------------------------------------------- -- declare all variables here ------------------------------------------------------------------------------------------------------------------- declare @Databases table (name varchar(255) not null); declare @SpaceUsageMonitor table (InstanceName varchar(50), [db_id] varchar(10), name varchar(50), growth varchar(50), SpaceUsedInKB varchar(50), SpaceUnusedInKB varchar(50), TotalSizeInKB varchar(50), FileLocation varchar(255)); declare @query varchar(max); declare @DBName varchar(255); declare @EmailProfileName varchar(255); declare @EmailRecipient varchar(255); declare @EmailBody varchar(max); ------------------------------------------------------------------------------------------------------------------- -- set all variables here ------------------------------------------------------------------------------------------------------------------- select top 1 @EmailProfileName = name FROM msdb.dbo.sysmail_profile order by name; set @EmailRecipient = 'dba@company.com' ------------------------------------------------------------------------------------------------------------------- -- build the report ------------------------------------------------------------------------------------------------------------------- insert into @Databases select d.name from sys.databases d where d.database_id > 6; while (select COUNT(*) from @Databases) > 0 begin select top 1 @DBName = name from @Databases order by name; select @query = 'use [' + @DBName + '];' + ' select HOST_NAME() as InstanceName , d.database_id as [db_id] , d.name , case when mf.is_percent_growth = 1 then cast(mf.growth as varchar) + ''%'' else cast((mf.growth * 8) as varchar) + ''KB'' end as growth , FILEPROPERTY(mf.name,''SpaceUsed'') * 8 as SpaceUsedInKB , (mf.size * 8) - (FILEPROPERTY(mf.name,''SpaceUsed'') * 8) as SpaceUnusedInKB , (mf.size * 8) as TotalSizeInKB , mf.physical_name as FileLocation from sys.databases d inner join sys.master_files mf on mf.database_id = d.database_id where d.database_id = DB_ID(''' + @DBName + ''');' insert into @SpaceUsageMonitor exec(@query); delete from @Databases where name = @DBName; end ------------------------------------------------------------------------------------------------------------------- -- set up the body of the email ------------------------------------------------------------------------------------------------------------------- select @EmailBody = ' ' select @EmailBody = @EmailBody + '' from @SpaceUsageMonitor select @EmailBody = @EmailBody + '

Instance Name	DB ID	DB Name	Growth	Spaced Used, KB	Space Unused, KB	Total Size, KB	File Location
' + InstanceName + '	' + [db_id] + '	' + name + '	' + growth + '	' + SpaceUsedInKB + '	' + SpaceUnusedInKB + '	' + TotalSizeInKB + '	' + FileLocation + '

' ------------------------------------------------------------------------------------------------------------------- -- send out the email ------------------------------------------------------------------------------------------------------------------- EXEC msdb.dbo.sp_send_dbmail @profile_name = @EmailProfileName, @recipients = @EmailRecipient, @subject = 'Databases Space Usage Report', @body = @EmailBody, @body_format = 'HTML' -------------------------------------------------------------------------------------------------------------------