CREATE TABLE [_DBA].DBO.DB_USE_REPORT(server_name NVARCHAR(20),

database_name  NVARCHAR(20),    file_group  NVARCHAR(20),    logical_name  NVARCHAR(30),    physical_name  NVARCHAR(200),

    type_desc NVARCHAR(20),    used_size_Mb int,    allocated_size_mb int,    max_size_Mb int,    growth int,    is_percent_growth int)

go

alter table [_DBA].DBO.DB_USE_REPORT  add inserttime  datetime default getdate() 

insert into [_DBA].DBO.DB_USE_REPORT

select @@SERVERNAME as server_name

      ,DB_NAME() as database_name

      ,case when data_space_id = 0 then 'LOG'

            else FILEGROUP_NAME(data_space_id)

            end as file_group

      ,name as logical_name

      ,physical_name

      ,type_desc

      ,FILEPROPERTY(name,'SpaceUsed')/128.0 as used_size_Mb

      ,size/128.0 as allocated_size_mb

      ,case when max_size = -1 then max_size

            else max_size/128.0

            end as max_size_Mb

      ,growth

      ,is_percent_growth,getdate()

 from sys.database_files

where state_desc = 'ONLINE'

USE [master]

GO

/****** Object:  StoredProcedure [dbo].[check_job_states]    Script Date: 2017/2/7 11:20:13 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        maxiangqian

-- Create date: 20170207

-- Description:    <monitor db use>

-- =============================================

CREATE PROCEDURE [dbo].[check_db_use]

AS

BEGIN

 DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =

    N'<H1>DB USED Report Last Week</H1>' +

    N'<table border="1">' +

    N'<tr><th>database_name</th><th>used_size_Mb</th>' +

    N'<th>allocated_size_mb</th><th>type_desc</th><th>inserttime</th>' +

    --N'<th>Expected Revenue</th></tr>' +

    CAST ( (

    SELECT  td =database_name     ,   '',

      td =used_size_Mb ,   '',

      td =allocated_size_mb,   '',

      td =type_desc,   '',

      td =inserttime   from [_DBA].DBO.DB_USE_REPORT  where inserttime> (getdate()-7) order by [database_name],type_desc,inserttime desc

              FOR XML PATH('tr'), TYPE

    ) AS NVARCHAR(MAX) ) +

    N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail     @profile_name = 'sendmail',

    @recipients='729975475@qq.com;',

    @subject = 'DB USED Report Last Week',

    @body = @tableHTML,

    @body_format = 'HTML' ;

END

GO