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.

IF OBJECT_ID('tempdb..##Temp') IS NOT NULL
  DROP TABLE ##Temp
CREATE TABLE ##Temp
  (
     DBName    VARCHAR(1000),
     TableName VARCHAR(1000)
  )
DECLARE @DBName NVARCHAR(MAX)

--Declare Cursor

DECLARE CUR CURSOR FOR
  --Get the list of online databases and exclude System databases
  SELECT name
  FROM   sys.databases
  WHERE  database_id > 4
         AND state = 0
         AND is_read_only = 0
--OPEN CURSOR

OPEN CUR
--Fetch First Row

FETCH NEXT FROM CUR INTO @DBName
--Loop

WHILE @@FETCH_STATUS = 0
  BEGIN
      DECLARE @SQL NVARCHAR(MAX)

      SET @SQL='Insert into ##Temp( DBName,TableName) 
      Select ''' + @DBName
               + ''',''[''+schema_name(schema_id)+''].[''+name+'']'' from ['
               + @DBName + '].sys.tables
where name like ''%[0-9]%'' OR name like ''%Bkp%''
OR name like ''%Backup%'''

      -- Print SQL for Debugging
      -- PRINT @SQL
      --Execute SQL to insert records in ##Temp table
      EXEC (@SQL)

      --Fetch next DataBase name 
      FETCH NEXT FROM CUR INTO @DBName
  END
--Close and Deallocate CursorCLOSE CUR
DEALLOCATE CUR
--Get DBName, table Names and Drop Statement

SELECT DBName,
       TableName,
       'Dropt able [' + DBName + '].' + TableName AS DropStatement

FROM   ##Temp