/*
	This script can be used to automatically rename a SQL Server and update the database mail display name
	based on the current computer host name
	
	Run this script after:
		Computer Name Changed
		Virtual Machine Cloned
*/

DECLARE
	@newHostName		SYSNAME		= HOST_NAME()
,	@oldHostName		SYSNAME
,	@newInstanceName	SYSNAME
,	@oldInstanceName	SYSNAME		= @@SERVERNAME
,	@accountName		SYSNAME

SELECT @oldHostName =	CASE
							WHEN CHARINDEX('\', @oldInstanceName) = 0 THEN @oldInstanceName
							ELSE LEFT(@oldInstanceName, CHARINDEX('\', @oldInstanceName) - 1)
						END

IF ( @oldHostName = @newHostName )
BEGIN
	PRINT('SQL Server is already named properly')
END
ELSE
BEGIN
	SELECT @newInstanceName = REPLACE(@oldInstanceName, @oldHostName, @newHostName)

	-- Rename SQL Server
	EXEC('EXEC sp_dropserver N''' + @oldInstanceName + '''')
	EXEC('EXEC sp_addserver N''' + @newInstanceName + ''', local')

	-- Update Database Mail Display Name
	WHILE EXISTS ( SELECT 1 FROM msdb..sysmail_account WHERE display_name = @oldHostName OR [display_name] = @oldInstanceName )
	BEGIN
		SELECT TOP 1 @accountName = [name] FROM msdb..sysmail_account WHERE [display_name] = @oldHostName OR [display_name] = @oldInstanceName
		EXEC('EXEC msdb..sysmail_update_account_sp @account_name = N''' + @accountName + ''', @display_name = N''' + @newInstanceName + '''')
	END

	PRINT('SQL Server renamed from ''' + @oldInstanceName + ''' to ''' + @newInstanceName + ''', restart SQL Server services to complete the changes')
END