DBA - Drop Backup Tables From All The Databases On A SQL Server

Scenario:

Sometime when we deploy code to QA/UAT/Production, depending
upon changes we take the backup. Sometime we take the backup of entire database
and if they changes are small and only happening to one table, we take the
backup of that table instead of taking the backup of entire database before
deployment.

Solution:

Below code can be used to find out all the tables which has
the numbers in their names (mostly we put date when backup the table or table
has Bkp or Backup in the name). If you are backing up your tables with
different name patterns, please change the select query according to that.

The code will run on all the databases on a server those are
online. It will return DBName,TableName with Schema and Drop statement. Please
review the list provided before you execute on any environment to make sure you
are dropping expected backup tables.