0

We were supplied an ETL package to run that updates tables from another server into our local db however when we run the package, there is a process that creates a new table for each table that gets updated that stores the dataset of the table before it was updated. We were told not to modify the existing package in any way but that we can add another task at the end to remove these tables.

Example;
Updating tbl name [dbo].[exampletbl]
Then creates 2 tables in the db
[dbo].[exampletbl]
[dbo].[exampletbl_old]

What we want to do is remove all the tables from that db that have been given the suffix _Old in a Execute SQL statement after the updating process has been completed.

How can this be achieved? I assume there is a way to completely remove all tables from a db where the name is like _Old.

PatBentley921
  • 45
  • 4

1 Answers1

-1

This creates a script which will drop all the tables named %_Old

SELECT 'DROP TABLE "' + TABLE_NAME + '"' 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE '%_Old'

Then run the script

(Ref: Drop all tables whose names begin with a certain string)

HSS
  • 178
  • 4
  • 2
    Perhaps not quite that query, else you can lose some gold -> https://dbfiddle.uk/O5PXTr1S – billinkc Dec 20 '22 at 14:06
  • 2
    Not to mention, that the customer might have been `told` they are being put on `hold` while someone finds out why they've been left in the `cold`. :) – Thom A Dec 20 '22 at 14:32
  • It would also be helpful, here, if you explain how you would run that script automagically in SSIS. The above isn't actually too useful on it's own, as just running the above in SSIS won't achieve anything. – Thom A Dec 20 '22 at 14:34