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
I began to treat my studies negatively, especially when they start to ask for essays or term papers, but this site https://www.masterpapers.com/ does any written work, which I am glad because it is convenient
ReplyDelete