Standardizing DBMAIL Profile & Account Setup for Notification

USE [db_maint]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
---------------------------------------------------------------------------------------------------------------------
IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'usp_dbMAilAccount')
                    AND type =  N'P')

DROP PROCEDURE [usp_dbMAilAccount];
GO
---------------------------------------------------------------------------------------------------------------------
Create Proc usp_dbMAilAccount (@profile_name nvarchar(400) = 'DBMail_Global_Profile', --newly added.
  @account_name nvarchar(400) = 'DBMail_Account', --newly added.
 -- @email_address nvarchar(400) = 'MSSQL_DB_Mail@mail.com',
      @display_name nvarchar(400) = 'DBMail',
      @description nvarchar(100) = 'Database Mail Account',
      @mailserver_name nvarchar(400) = 'mail_server_name',
      @mailserver_type nvarchar(100) = 'SMTP',
      @replyto_address nvarchar(100) = NULL,
      @port INT = 25,
  @recipients nvarchar(500) = 'youremail@mail.com',
  --@recipients nvarchar(500) = 'xyz@mail.com',
      @Option nvarchar(5) = 'C')

as
--------------------------------------------------------------------------------------------------------------------
/*
Purpose: The purpose of this procedure is to create / setup/ delete/ update database mail account.
         
 First it will cleanup all existing accounts and followed by setting up the fresh accounts.
----------------------------------------------------------------------------------------------------------------------
**RUN : 
-- To Create the DB mail account
EXEC usp_dbMAilAccount @recipients = 'xyz@mail.com'

-- To Delete the DB mail account
EXEC usp_dbMAilAccount @Option ='C'
----------------------------------------------------------------------------------------------------------

Important Procedures: 1. sysmail_add_profileaccount_sp
  2. sysmail_help_principalprofile_sp
  3. sp_send_dbmail
----------------------------------------------------------------------------------------------------------
EXEC msdb.dbo.sysmail_help_configure_sp;
EXEC msdb.dbo.sysmail_help_account_sp;
EXEC msdb.dbo.sysmail_help_profile_sp;
EXEC msdb.dbo.sysmail_help_principalprofile_sp;
---------------------------------------------------------------------------------------------------------------------

Key Procedures to Note & Remember:
1. sysmail_add_account_sp
2. sysmail_add_profile_sp
3. sysmail_add_profileaccount_sp
4. sysmail_add_principalprofile_sp
5. sp_send_dbmail
6. sysmail_delete_profileaccount_sp
7. sysmail_delete_profile_sp
8. sysmail_delete_account_sp
------------------------------------------------------------------------
--Profiles
SELECT * FROM msdb.dbo.sysmail_profile
------------------------------------------------------------------------------
--Accounts
SELECT * FROM msdb.dbo.sysmail_account
------------------------------------------------------------------------------
--Profile Accounts
select * from msdb.dbo.sysmail_profileaccount
------------------------------------------------------------------------------
--Principal Profile
select * from msdb.dbo.sysmail_principalprofile
------------------------------------------------------------------------------
--Mail Server
SELECT * FROM msdb.dbo.sysmail_server
SELECT * FROM msdb.dbo.sysmail_servertype
SELECT * FROM msdb.dbo.sysmail_configuration
------------------------------------------------------------------------------
--Email Sent Status
SELECT * FROM msdb.dbo.sysmail_allitems
SELECT * FROM msdb.dbo.sysmail_sentitems
SELECT * FROM msdb.dbo.sysmail_unsentitems
SELECT * FROM msdb.dbo.sysmail_faileditems
--------------------------------------------------------------------------------------------------------------------------------
--Email Status
SELECT SUBSTRING(fail.subject,1,25) AS 'Subject',       fail.mailitem_id,       LOG.descriptionFROM msdb.dbo.sysmail_event_log LOG
join msdb.dbo.sysmail_faileditems fail ON fail.mailitem_id = LOG.mailitem_id WHERE event_type = 'error'
--------------------------------------------------------------------------------------------------------------------------------
 --Mail Queues
EXEC msdb.dbo.sysmail_help_queue_sp
--------------------------------------------------------------------------------------------------------------------------------
--DB Mail Status
EXEC msdb.dbo.sysmail_help_status_sp
--------------------------------------------------------------------------------------------------------------------------------------    
Key Queries:

select * from msdb.dbo.sysmail_profileaccount
select * from msdb.dbo.sysmail_profile
select * from msdb.dbo.sysmail_account

--SELECT * FROM msdb.dbo.sysmail_profileaccount pa
--      JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
--      JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id

----------------------------------------------------------------------------------------------------------------------------------------------
-- Delete Unsent Emails.
 SELECT * FROM msdb.dbo.sysmail_event_log;
-- To get number of unsent emails
select count(*) from msdb.dbo.sysmail_unsentitems;
-- remove all the unsent emails
delete from msdb.dbo.sysmail_unsentitems;

----------------------------------------------------------------------------------------------------------------------------------------------
-- Delete failed alerts older than 2 days.
DECLARE @Date datetime
SELECT @Date = DateAdd(dd,-2,Getdate())
SELECT @Date
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @Date , @sent_status = 'failed' 
------------------------------------------------------------------------------------------------------------------------------------------
-- Here is how we will delete all events in the log older than two days
DECLARE @Date datetime
SELECT @Date = DateAdd(dd,-2,Getdate())

EXECUTE msdb.dbo.sysmail_delete_log_sp @logged_before = @Date 
----------------------------------------------------------------------------------------------------------------------------------------------
*/BEGIN
BEGIN TRY
SET NOCOUNT ON

--Is db mail enabled for use?
IF NOT EXISTS (SELECT value_in_use
               FROM sys.configurations
               WHERE name = 'Database Mail XPs'
               AND value_in_use = 1)
BEGIN
   EXEC ('sp_configure ''show advanced options'', 1')
   RECONFIGURE;
   EXEC ('sp_configure ''Database Mail XPs'', 1')
   RECONFIGURE;
   EXEC ('sp_configure ''show advanced options'', 0')
   RECONFIGURE;
END
----------------------------------------------------------------------------------------------------------------------------
-- Generate logic to build account name.
declare @servername sysname = (select @@servername);
--declare @account_name nvarchar(1200) = 'DBMail' ;
-- declare @profile_name nvarchar(1200);
declare @email_address nvarchar(1200)= 'DBMAIL';

--DBMail_LVDB0001_SPE
--select @backup_path 
          DECLARE @len   INT, 
                  @i     INT, 
                  @Left  nVARCHAR(4000), 
                  @right nVARCHAR(4000),
  @full_string nVARCHAR(4000);

          --SET @SERVERNAME = (SELECT @@servername); 
          --select @backup_path AS [TLOG Backup PATH] 
          --PRINT @backup_path 
          IF @SERVERNAME LIKE '%\%' 
            BEGIN 
                SET @Left= (SELECT LEFT(@SERVERNAME, Charindex('\', @SERVERNAME) 
                                                     - 1)) 
                --SELECT @Left AS [DEFAULT SERVER NAME] 
                SET @len = (SELECT Len(@SERVERNAME)) 
                --SELECT @LEN 
                SET @right = (SELECT Substring(@SERVERNAME, 
                                     Charindex ('\', @SERVERNAME) + 
                                     1 
                                     , @len)) 
             --SELECT @right AS [SQL INSTANCE NAME] 
 SET @full_string = @Left + '_'+ @right;
--PRint @full_string
            END 
          ELSE 
            BEGIN 
                SELECT @left = @SERVERNAME;
SET @full_string = @left;
--PRint @full_string

            END 

-- Build the final account name.
SET @email_address = @email_address + '_'+ @full_string+'@mail.com';
--Print @email_address

SET @display_name = @display_name + '_' + @full_string;
--Print @display_name

---------------------------------------------------------------------------------------------------------------------
--declare @ProfileID INT;

----Does the profile exist already?
--SELECT @ProfileID = p.profile_id
----SELECT p.name, *
--FROM msdb.dbo.sysmail_profileaccount pa
--JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
--full outer JOIN msdb.dbo.sysmail_account a ON a.account_id=pa.account_id
--WHERE p.name = @profile_name; 
---------------------------------------------------------------------------------------------------------------------
--SELECT * FROM msdb.dbo.sysmail_profileaccount pa
--      JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
--      JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id

----------------------------------------------------------------------------------------------------------------------
-- Clean up the unnecesassary db accounts and profiles. We need one global profile to send email alerts. [Mandatory Step]

--IF EXISTS(
--SELECT * FROM msdb.dbo.sysmail_profileaccount pa       JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
--      JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id WHERE       p.name = @ProfileName AND
--      a.name = @AccountName)
--BEGIN
--      PRINT 'Deleting Profile Account'
--      EXECUTE sysmail_delete_profileaccount_sp       @profile_name = @ProfileName,       @account_name = @AccountName
--END
-- IF EXISTS(
--SELECT * FROM msdb.dbo.sysmail_profile p WHERE p.name = @ProfileName)
--BEGIN
--      PRINT 'Deleting Profile.'      EXECUTE sysmail_delete_profile_sp       @profile_name = @ProfileName
--END
-- IF EXISTS(
--SELECT * FROM msdb.dbo.sysmail_account a WHERE a.name = @AccountName)
--BEGIN
--      PRINT 'Deleting Account.'        EXECUTE sysmail_delete_account_sp       @account_name = @AccountName
--END

---------------------------------------------------------------------------------------------------------------------------------------
select * from msdb.dbo.sysmail_profile 
select * from msdb..sysmail_profileaccount
select * from msdb.dbo.sysmail_account 
---------------------------------------------------------------------------------------------------------------------------------------

create table #temp2 (profile_id INT, account_id INT, Profile_name nvarchar(1000), account_name nvarchar(1000), display_name nvarchar(1000), last_modified_datetime nvarchar(1000), last_modified_user nvarchar(1000))

INSERT INTO #temp2
SELECT p.profile_id, a.account_id, p.name,a.name, a.display_name,a.last_mod_datetime,a.last_mod_user 
    FROM msdb.dbo.sysmail_profileaccount pa
      full outer JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
     full outer JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id

--select * from #temp2

declare @profile_id3 INT, @Profile_name3 nvarchar(1000),@account_name3 nvarchar(1000);

select @profile_id3 = profile_id from #temp2;

select @Profile_name3 = Profile_name from #temp2;

select @account_name3 = account_name from  #temp2;

IF ((@profile_id3 IS NULL) and (@Profile_name3 IS NULL)) 
BEGIN
  
        DELETE FROM msdb.dbo.sysmail_account
WHERE name = @account_name3

  --   PRINT 'Deleting Account, when there''s NO Profile Available (i.e. PRofile_ID and Profile_NAme Both NULL) := ' + @account_name3;
 --EXECUTE msdb..sysmail_delete_account_sp       @account_name = @account_name3;   

--declare @cmd2 nvarchar(4000)
--if not exists ( select d.is_encrypted, d.name, k.* from sys.dm_database_encryption_keys k inner join sys.databases d on d.database_id = k.database_id
--                where d.name = @db_nm and is_encrypted = 1) 
--begin
--    select @cmd2 = 'USE ' + quotename(@db_nm) + ';' + CHAR(13)+
--        'CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE ' + quotename(@cert_nm) + ';'
   
--  Print @cmd2
--   EXEC sp_executesql @cmd2;
--END
--ELSE
--BEGIN
-- PRINT 'DATABASE ENCRYPTION KEY Already Exists Against the Database:= ' + @db_nm
--END

END
--select * from #temp2

drop table #temp2
------------------------------------------------------------------------------------------------------------------------------------------
create table #temp (profile_id INT, account_id INT, Profile_name nvarchar(1000), account_name nvarchar(1000), display_name nvarchar(1000), last_modified_datetime nvarchar(1000), last_modified_user nvarchar(1000))

INSERT INTO #temp
SELECT p.profile_id, a.account_id, p.name,a.name, a.display_name,a.last_mod_datetime,a.last_mod_user 
    FROM msdb.dbo.sysmail_profileaccount pa
      full outer JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
     full outer JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
--SELECT p.profile_id, a.account_id, p.name as 'Profile_name' ,a.name as 'account_name', a.display_name,a.last_mod_datetime,a.last_mod_user 
--    FROM msdb.dbo.sysmail_profileaccount pa
--      inner JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
--     full outer JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
--where p.profile_id IS NOT NULL and a.account_id IS NOT NULL;

--select * from #temp

---------------------------------------------------------------------------------------------------------------------------------------
declare @profile_id1 INT, @account_id1 INT, @Profile_name1 nvarchar(1000), @account_name1 nvarchar(1000);

DECLARE Cur1 CURSOR FOR
SELECT profile_id, account_id , Profile_name , account_name FROM #temp

OPEN Cur1;
FETCH NEXT FROM Cur1 INTO @profile_id1 ,  @account_id1, @Profile_name1, @account_name1;

WHILE @@FETCH_STATUS = 0
   BEGIN
    
 --print @profile_id1
 --print @account_id1
---------------------------------------------------------------------------------------------------------------------------------------
--IF EXISTS(
--SELECT * FROM msdb.dbo.sysmail_profileaccount pa       JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
--      JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id WHERE       p.name = @ProfileName AND
--      a.name = @AccountName)
--BEGIN
--      PRINT 'Deleting Profile Account'
--      EXECUTE sysmail_delete_profileaccount_sp       @profile_name = @ProfileName,       @account_name = @AccountName
--END
-- IF EXISTS(
--SELECT * FROM msdb.dbo.sysmail_profile p WHERE p.name = @ProfileName)
--BEGIN
--      PRINT 'Deleting Profile.'      EXECUTE sysmail_delete_profile_sp       @profile_name = @ProfileName
--END
-- IF EXISTS(
--SELECT * FROM msdb.dbo.sysmail_account a WHERE a.name = @AccountName)
--BEGIN
--      PRINT 'Deleting Account.'        EXECUTE sysmail_delete_account_sp       @account_name = @AccountName
--END
---------------------------------------------------------------------------------------------------------------------------------------
 --Delete from msdb.dbo.sysmail_profileaccount
 --where profile_id = @profile_id1 and account_id = @account_id1;

  IF EXISTS(
SELECT * FROM msdb.dbo.sysmail_profileaccount pa       JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
  JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id WHERE       p.name = @Profile_name1 AND
  a.name = @account_name1)
BEGIN

  PRINT 'Deleting Profile Account:= ' + @Profile_name1;
      EXECUTE msdb..sysmail_delete_profileaccount_sp   @profile_name = @Profile_name1,  @account_name = @account_name1;

  END
  
 --DELETE FROM msdb.dbo.sysmail_profile 
 --where profile_id = @profile_id

   IF EXISTS(
SELECT * FROM msdb.dbo.sysmail_profile p WHERE p.name = @Profile_name1)
BEGIN

  PRINT 'Deleting Profile:= ' + @Profile_name1    
  EXECUTE msdb..sysmail_delete_profile_sp       @profile_name = @Profile_name1;
  END

 --DELETE FROM msdb.dbo.sysmail_account
 --WHERE account_id  = @account_id1;

IF EXISTS(
SELECT * FROM msdb.dbo.sysmail_account a WHERE a.name = @account_name1)
BEGIN

 PRINT 'Deleting Account:= ' + @account_name1      
 EXECUTE msdb..sysmail_delete_account_sp       @account_name = @account_name1;
 END

FETCH NEXT FROM Cur1 INTO @profile_id1 ,  @account_id1, @Profile_name1, @account_name1;
   END;

CLOSE Cur1;
DEALLOCATE Cur1;

drop table #temp
---------------------------------------------------------------------------------------------------------------------
create table #temp1 (profile_id INT, account_id INT, Profile_name nvarchar(1000), account_name nvarchar(1000), display_name nvarchar(1000), last_modified_datetime nvarchar(1000), last_modified_user nvarchar(1000))

INSERT INTO #temp1
SELECT p.profile_id, a.account_id, p.name,a.name, a.display_name,a.last_mod_datetime,a.last_mod_user 
    FROM msdb.dbo.sysmail_profileaccount pa
      full outer JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
     full outer JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id

--select * from #temp1

/*
declare @profile_id2 INT, @Profile_name2 nvarchar(1000);

select @profile_id2 = profile_id from #temp1;

select @Profile_name2 = Profile_name from #temp1;

IF (@profile_id2 IS NULL) and (@Profile_name2 IS NULL)
BEGIN
  
     PRINT 'Deleting Account, when there''s NO Profile Available (i.e. PRofile_ID and Profile_NAme Both NULL) := ' + @account_name1      
 EXECUTE msdb..sysmail_delete_account_sp       @account_name = @account_name1;   
END

*/
drop table #temp1
-----------------------------------------------------------------------------------------------------------------------------
IF @Option = 'C'
BEGIN

IF exists (select * from msdb.dbo.sysmail_account where name = @account_name)
BEGIN
PRint 'Database Mail Account ' + quotename (@account_name) + ' is already setup in the Server:= ' + quotename(@servername);
Print 'NOT Allowed to Create DB Mail Account Multiple Time.'

--select * from msdb.dbo.sysmail_account;

--select * from msdb.dbo.sysmail_account where name = @account_name;

END

ELSE IF NOT exists (select * from msdb.dbo.sysmail_account where name = @account_name)
BEGIN
PRINT 'Creating Account: ' + @account_name;

--select * from msdb.dbo.sysmail_account where name = @account_name
-----------------------------------------------------------------------------------------------------------------------------------------
-- To setup database mail.

-- Steps1:  EXECUTE msdb.dbo.sysmail_add_profile_sp
-- step 2:  EXECUTE msdb.dbo.sysmail_update_account_sp
-- Step 3:  EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
-- Step 4:  EXECUTE msdb.dbo.sysmail_add_account_sp
-- Step 5:  EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
-----------------------------------------------------------------------------------------------

--EXECUTE msdb.dbo.sysmail_add_profile_sp
--       @profile_name = @profile_name,
--       @description = @description;

-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- STEP 1 Create Email Account in SQL Server:
-----------------------------------------------------------------------------------------------------------------
-- Setup mail account
declare @account_id int
execute msdb.dbo.sysmail_add_account_sp
@account_name= @account_name,
@email_address= @email_address, 
@display_name= @display_name,
@description = @description,
@mailserver_name= @mailserver_name,
@mailserver_type = @mailserver_type,
@port = @port,
@username=null,
@password=null,
@enable_ssl=0,
--@username=null,
--@password=null,
@use_default_credentials=1,
@account_id=@account_id output;
---------------------------------------------------------------------------------------------------------------------
--select @account_id
--select * from msdb.dbo.sysmail_account

------------------------------------------------------------------------------
-- to update the above details to add user_name, pswd = NULL, use the updte script.
--declare @account_id int
execute msdb.dbo.sysmail_update_account_sp
@account_id= @account_id, -- Pass the correct account ID.
@account_name=@account_name,
@email_address=@email_address,
@display_name=@display_name,
@description=@description,
@replyto_address= @replyto_address,
@mailserver_name=@mailserver_name,
@mailserver_type=@mailserver_type,
@port=@port,
@username=null,
@password=null,
@use_default_credentials=1,
@enable_ssl=0;
------------------------------------------------------------------------------
-- STEP 2 Creat Email Profile in SQL Server:
-- CREATE PROFILE

SET @description = 'Database Mail Profile';
EXECUTE msdb.dbo.sysmail_add_profile_sp
       @profile_name = @profile_name, 
       @description = @description;
------------------------------------------------------------------------------
-- Set the New Profile as the Default
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = @profile_name,
    @principal_name = 'public',
    @is_default = 1 ;
---------------------------------------------------------------------------------------------------------------------
-- STEP 3 Link email account to email profile:
-- LINK ACCOUNT TOPROFILE
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp   
@profile_name = @profile_name,
@account_name = @account_name,
@sequence_number = 1
---------------------------------------------------------------------------------------------------------------------
PRint 'Database Mail Account Has been Setup ' + quotename (@account_name) + ' is already setup in the Server:= ' + quotename(@servername);
---------------------------------------------------------------------------------------------------------------------------------------

--Create primary profile; we always want a profile for DBOPS team.
--IF @ProfileID IS NULL
--BEGIN
--      PRINT 'Creating profile: ' + @account_name;
--      EXEC msdb.dbo.sysmail_add_profile_sp
--            @profile_name = @account_name,
--            @description = @description,
--            @profile_id = @ProfileID output

--Print @ProfileID
---------------------------------------------------------------------------------------------------

   --IF (@profile_id IS NOT NULL) -- use id
   --BEGIN
      --SELECT @profileid = profile_id FROM msdb.dbo.sysmail_profile WHERE profile_id=@profile_id
      --IF (@profileid IS NULL) -- id is invalid
      --BEGIN
      --   RAISERROR(14606, -1, -1, 'profile')
      --   RETURN(3)
      --END     

-- SET @ProfileName = (select name from msdb..sysmail_profile where profile_id = @cnt)

--SET @Sub = 'Testing Email From ' + quotename(@Server) + ' from profile : ' + @ProfileName

--declare @Profile_Name1 nvarchar(400);
/*
SELECT @profileid = profile_id FROM msdb.dbo.sysmail_profile WHERE name like '%DBMail_%';

select @Profile_Name = Name FROM msdb.dbo.sysmail_profile WHERE name like '%DBMail_%';

 IF (@Profile_Name1 is not null)
 BEGIN

   Print @profileid;
  Print @Profile_Name

      -- give everybody access to use this profile and make it the default
      EXEC msdb.dbo.sysmail_add_principalprofile_sp
            @profile_id = @profileid,
            @principal_name = 'public',
            @is_default = 1

END
*/---------------------------------------------------------------------------------------------------------------------------------------
END

/*
ELSE IF @Option = 'D'
BEGIN
Print 'delete'
IF exists (select * from msdb.dbo.sysmail_account where name = @account_name)
BEGIN

print 'db mail account ' + quotename (@account_name) + ' exists. Can be Deleted.'
-- Delete the mail account:=
--delete from msdb.dbo.sysmail_account
--where name = @account_name;

    Print @profile_id
Print @account_id1
Print @account_name;

 Delete from msdb.dbo.sysmail_profileaccount
 where profile_id = @profile_id and account_id = @account_id1;

  --PRINT 'Deleting Profile.'      EXECUTE msdb..sysmail_delete_profile_sp       @profile_name = @Profile_name;

 DELETE FROM msdb.dbo.sysmail_profile 
 where profile_id = @profile_id

 --PRINT 'Deleting Account.'        EXECUTE msdb..sysmail_delete_account_sp       @account_name = @account_name1;

 DELETE FROM msdb.dbo.sysmail_account
 WHERE account_id  = @account_name;


print quotename (@account_name) + ' is successfully Deleted From the Server:= ' + quotename(@servername); 

END


ELSE IF NOT exists (select * from msdb.dbo.sysmail_account where name = @account_name)
BEGIN


Print 'db mail account ' + quotename (@account_name) + ' deleted already From the Server:= ' + quotename(@servername);
Print 'Can''t be deleted again.'
END
END
*/---------------------------------------------------------------------------------------------------------------------
--- Clean Up All Other DB Mail Accounts Other than Server DB Mail Account.

select * from msdb.dbo.sysmail_profileaccount
select * from msdb.dbo.sysmail_profile
select * from msdb.dbo.sysmail_account
---------------------------------------------------------------------------------------------------------------------
SELECT * FROM msdb.dbo.sysmail_profileaccount pa
      JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
      JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
---------------------------------------------------------------------------------------------------------------------
/*
IF EXISTS(
SELECT * FROM msdb.dbo.sysmail_profileaccount pa
      JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
      JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
WHERE
      p.name != @account_name AND
      a.name != @account_name)
BEGIN

--declare @count INT = 1, @tot INT;

--SET @tot = (SELECT count(*) FROM msdb.dbo.sysmail_profileaccount pa
--      JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
--      JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
--WHERE
--      p.name != @account_name AND
--      a.name != @account_name)
------------------------------------------------------------------------------------------------------

create table #temp (profile_id INT, account_id INT, Profile_name nvarchar(1000), display_name nvarchar(1000), last_modified_datetime nvarchar(1000), last_modified_user nvarchar(1000))

INSERT INTO #temp
SELECT * FROM msdb.dbo.sysmail_profileaccount pa
      JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
      JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id


select * from #temp

--WHILE(@count <= @tot)
 --BEGIN
      PRINT 'Deleting Profile Account';
  
--   IF (@profileid IS NOT NULL AND @accountid IS NOT NULL) -- both parameters supplied for deletion

      DELETE FROM msdb.dbo.sysmail_profileaccount

      WHERE profile_id=@profileid AND account_id=@accountid

  select * from msdb.dbo.sysmail_profileaccount;


  select * from msdb.dbo.sysmail_profile 

   --ELSE IF (@profileid IS NOT NULL) -- profile id is supplied

   --   DELETE FROM msdb.dbo.sysmail_profileaccount

   --   WHERE profile_id=@profileid



   --ELSE IF (@accountid IS NOT NULL) -- account id is supplied

   --   DELETE FROM msdb.dbo.sysmail_profileaccount

   --   WHERE account_id=@accountid



   --ELSE -- no parameters are supplied for deletion

   --BEGIN

   --   RAISERROR(14608, -1, -1, 'profile', 'account')  

   --   RETURN(3)   

   --END



      --EXECUTE msdb..sysmail_delete_profileaccount_sp
      --@profile_name = @account_name,
      --@account_name = @account_name
--END
------------------------------------------------------------------------------------------------------
IF EXISTS(
SELECT * FROM msdb.dbo.sysmail_profile p
WHERE p.name = @account_name)
BEGIN
      PRINT 'Deleting Profile.'
  
 DELETE FROM msdb.dbo.sysmail_profile 
 WHERE name ! = @account_name

      --EXECUTE msdb..sysmail_delete_profile_sp
      --@profile_name = @account_name
END
 
IF EXISTS(
SELECT * FROM msdb.dbo.sysmail_account a
WHERE a.name = @account_name)
BEGIN
      PRINT 'Deleting Account.'

     DELETE FROM msdb.dbo.sysmail_account

        WHERE name != @account_name


      --EXECUTE msdb..sysmail_delete_account_sp
      --@account_name = @account_name
END

--END
END
*/----------------------------------------------------------------------------------------------------------------------------------------------------------
-- Test whether the dbmail account has been setup correctly. It should have one entry, so recipient should receive only a single email alert from each server.

DECLARE
 @Total int
 ,@cnt int
 ,@ProfileName nvarchar(1000)
 --,@AccountName nvarchar(4000)
 ,@Server varchar(500)
 ,@Sub nvarchar(4000)
 ,@body nvarchar(4000),
 @cnt_Accounts INT;

SET @Total = (SELECT MAX(profile_id) FROM msdb..sysmail_profile)
 SET @cnt = 1

--SET @cnt_Accounts = (select count(*) from msdb..sysmail_account)

--DECLARE @Table TABLE (account_name varchar(400),account_id INT)

--INSERT INTO @TABLE
--select name, account_id from msdb..sysmail_account
--Order by Account_id

--select * from @TABLE

DECLARE @tableHTML  NVARCHAR(4000) ;
-------------------------------------------------------
--select * from msdb.dbo.sysmail_profileaccount
--select * from msdb.dbo.sysmail_profile
--select * from msdb.dbo.sysmail_account
------------------------------------------------------

SET @tableHTML =
    N'
#box-table
{
font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
font-size: 12px;
text-align: center;
border-collapse: collapse;
border-top: 7px solid #9baff1;
border-bottom: 7px solid #9baff1;
}
#box-table th
{
font-size: 13px;
font-weight: normal;
background: #b9c9fe;
border-right: 2px solid #9baff1;
border-left: 2px solid #9baff1;
border-bottom: 2px solid #9baff1;
color: #039;
}
#box-table td
{
border-right: 1px solid #aabcfe;
border-left: 1px solid #aabcfe;
border-bottom: 1px solid #aabcfe;
color: #669;
}
tr:nth-child(odd){ background-color:#eee; }
tr:nth-child(even){ background-color:#fff; }
'
+


    N'<H1><font color="red"> Global Mail Profile & Accounts Configured (Server :  ' + @full_string + ')' + ' </font></H1>' + '<BR>' + '<BR>' + 

N'<table id="box-table" border="1">' +
    N'<tr><th>Profile_ID</th>'+
N'<th>Profile_Name</th>'+
N'<th>Profile_Description</th>'+
N'<th>Last_Mod_Datetime</th>'+
    N'<th>Last_Mod_User</th></tr>' +
    CAST ( ( SELECT td = profile_id, '',
                    td = name, '',
td = [description], '',
td = convert(varchar(19), [last_mod_datetime],121) , '',
td = [last_mod_user], ''
              FROM msdb.dbo.sysmail_profile
              ORDER BY profile_id ASC
             FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
N'</table>'

+ 
'<BR>' + '<BR>' + '<BR>' +
    N'<table id="box-table" border="1">' +
    N'<tr><th>Account_ID</th>'+
N'<th>Account_Name</th>'+
N'<th>Account_Description</th>'+
N'<th>Email_Address</th>'+
    N'<th>Display_Name</th>'+
    N'<th>Last_Mod_Datetime</th>' +
    N'<th>Last_Mod_User</th></tr>' +
 CAST ( ( SELECT td = account_id, '',
                    td = name, '',
td = [description], '',
td = email_address, '',
td = display_name, '' ,
td = convert(varchar(19), [last_mod_datetime],121) , '',
    td = last_mod_user, ''
              FROM msdb.dbo.sysmail_account
              ORDER BY account_id ASC
             FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>';

select @tableHTML;


------------------------------------------------------------------------------------------------------
SET @Server = @@SERVERNAME

WHILE(@cnt <= @Total)
 BEGIN
 SET @ProfileName = (select name from msdb..sysmail_profile where profile_id = @cnt)

 --SET @AccountName = (select * from msdb..sysmail_account where @account_id = @cnt)

SET @Sub = 'Testing Email From ' + quotename(@Server) + ' from profile : ' + @ProfileName;

 IF (@ProfileName is not null)
 BEGIN

--Print '------------------------------'
--PRINT @ProfileName
--Print @AccountName
--PRINT @Sub
--Print '------------------------------'

----------------------------------------------------------------------------------------------------------------------------------------
EXEC msdb.dbo.sp_send_dbmail
   @recipients = @recipients
  , @subject = @Sub
  , @body = @tableHTML
  , @profile_name = @ProfileName
 , @body_format = 'HTML' ;
 END

SET @cnt = @cnt + 1
END

END
------------------------------------------------------------------------------------------------------------------------------------
SET NOCOUNT OFF
END try 

    BEGIN catch 
        DECLARE @ErrorNumber INT; 
        DECLARE @ErrorSeverity INT; 
        DECLARE @ErrorState INT; 
        DECLARE @ErrorLine INT; 
        DECLARE @ErrorProcedure NVARCHAR(4000); 
        DECLARE @ErrorMessage NVARCHAR(4000); 

        SELECT @ErrorNumber = Error_number(), 
               @ErrorSeverity = Error_severity(), 
               @ErrorState = Error_state(), 
               @ErrorLine = Error_line(), 
               @ErrorProcedure = Error_procedure(); 

        SELECT @ErrorMessage = 
               N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' 
               + 'Message: ' + Error_message(); 

        SELECT @ErrorMessage AS [Error_Message]; 

        SELECT @ErrorProcedure AS [Error_Procedure]; 

        PRINT 'Error ' 
              + CONVERT(VARCHAR(50), Error_number()) 
              + ', Severity ' 
              + CONVERT(VARCHAR(5), Error_severity()) 
              + ', State ' 
              + CONVERT(VARCHAR(5), Error_state()) 
              + ', Procedure ' 
              + Isnull(Error_procedure(), '-') + ', Line ' 
              + CONVERT(VARCHAR(5), Error_line()); 

        PRINT Error_message(); 
    END catch 

    SET nocount OFF 
END
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO