Sending query based result set via T-SQL in SQL Server

We used to created databases on the fly by different applications as DBA my team need to know what all databases has been created today. I have developed below script to fetch the list of the databases and send it via sp_send_dbmail.

In order to send email via "sp_send_dbmail" all you need is to create a database mail profile. You can see here how to configure database mail.

Once your DB mail profile is configured, you can use this as a example to send email, you can further modify t-SQL and XML as per your need.

I hope this will help.

CREATE TABLE #tblDBList (RecordID INT IDENTITY(1,1), DBName nVARCHAr(250), CrDate DATETIME)

INSERT INTO #tblDBList (DBName, CrDate)

SELECT name, create_date from sys.databases WHERE CAST(create_date AS DATE)  =  DATEADD(Day, -1, CAST(GETDATE() AS DATE))  ORDER BY create_date

IF EXISTS (SELECT 1 FROM #tblDBList)

BEGIN

      DECLARE

            @xml NVARCHAR(MAX),

            @body NVARCHAR(MAX),

            @ServerName VARCHAR(25),

            @Subject VARCHAR(50)

      SELECT @ServerName = @@SERVERNAME

     

      SET @Subject = 'New Database(s) : ' + @ServerName

      SET @xml = CAST(( SELECT

                              [RecordID] AS 'td',''

                              ,[DBName] AS 'td',''

                              ,CONVERT(VARCHAR, [CrDate], 100) AS 'td',''

      FROM  #tblDBList ORDER BY [RecordID]

      FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

     

      SET @body ='

                             

                                   

Following database(s) has been created on '

+ @ServerName + '

                                   

                                                                            

                                         
S. No.

                                         
Database Name

                                         
CreatedOn (CST)

                                   

'   

     

      SET @body = @body + @xml +'

'

      SET @body = @body + '

This email has been generated by SQL Server as a routine notification.

'

      SET @body = @body + '

'

     

      EXEC msdb.dbo.sp_send_dbmail

      @profile_name = '***Server_DBMail_Profile', -- replace with your SQL Database Mail Profile

      @body = @body,

      @body_format ='HTML',

      @recipients = 'Ashish.Upadhyay@******.***', -- replace with your email address

      @subject = @Subject

     

      END

     

            DROP TABLE #tblDBList

      GO