Remove Backup History for databases no longer on the server

USE [msdb]
;
DECLARE @DBName NVARCHAR(128)
DECLARE @Delete nvarchar(max)

CREATE TABLE #DatabaseList
    (
    databasename nvarchar(128),
    deletestatus bit
    );
   
INSERT INTO #DatabaseList
SELECT DISTINCT database_name,
                0
FROM msdb.dbo.backupset
WHERE database_name NOT IN(
    SELECT name
    FROM sys.databases)
   
WHILE (SELECT COUNT(*) FROM #databaseList WHERE deleteStatus = 0) > 0
    BEGIN

        SELECT TOP 1 @DBName = databasename
        FROM #databaseList
        WHERE DeleteStatus = 0;

            SET @Delete = 'EXEC [msdb].[dbo].[sp_delete_database_backuphistory] @database_name = N''' +@DBName + ''''
   
            EXEC (@Delete)
   
            IF @@ERROR<>0 PRINT @Delete
   
        UPDATE #databaseList
        SET deletestatus = 1
        WHERE databaseName = @DBName;

    END

DROP TABLE #databaseList;

Database Deletion Taking a long time

This is caused because there are missing indexes on the backupset table within the system database msdb:

After using the Display Estimated Query Plan Wizard a few times for the system stored procedure

exec dbo.sp_delete_database_backuphistory @database_name =  N'DBName'

I found the follwoing indexes needed to be added to the backupset table:

USE [msdb]
GO

CREATE NONCLUSTERED INDEX [idx_backupset_database_name_backup_set_id] ON [dbo].[backupset]
(
    [database_name] ASC
)
INCLUDE ( [backup_set_id]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [idx_backupset_database_name_media_set_id] ON [dbo].[backupset]
(
    [database_name] ASC
)
INCLUDE ( [media_set_id]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [idx_backupset_media_set_id] ON [dbo].[backupset]
(
    [media_set_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Getting Column Descriptions of a Table in MS SQL

--Run in the Context of the Database where the table resides...

DECLARE @Schema VARCHAR(50)
DECLARE @Table VARCHAR(50)

SET @Schema = 'Your Schema Here'
SET @Table = 'Your Table Here'


SELECT sch.name AS [Schema],
        st.name AS [Table],
        sept.value AS [TableDescription],
        sc.name AS [Column],
        sep.value AS [ColumnDescription],
        ty.name AS [DataType],
        sc.max_length AS [DataLength],
        sc.precision AS [DatePrecision],
        sc.scale AS [DateScale],
        sc.collation_name AS [ColumnCollation]
FROM sys.tables st
INNER JOIN sys.schemas sch ON st.schema_id = sch.schema_id
LEFT JOIN sys.extended_properties sept ON st.object_id = sept.major_id
                                        AND st.parent_object_id = sept.minor_id
                                        AND sept.name = 'MS_Description'
INNER JOIN sys.columns sc ON st.object_id = sc.object_id
LEFT JOIN sys.extended_properties sep ON st.object_id = sep.major_id
                                        AND sc.column_id = sep.minor_id
                                        AND sep.name = 'MS_Description'
INNER JOIN sys.types ty ON sc.user_type_id = ty.user_type_id
WHERE st.name = @Table
AND sch.name = @Schema
;