cleanup/dropping of all interimtables

Question

cleanup/dropping of all interimtables

rmurray 17656

Valued Member

Points: 70
More actions
May 8, 2025 at 9:20 am

#4588956

Is there a [best] method of dropping all interim tables within a Azure SQL database. I have inadvertently, with some pipeline fails, created an awful lot of them.
If there is a way to do this in one query it would be really helpful.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113536 More actions · Answer 1

Do all your interim tables have some sort of static prefix or suffix that differentiates them from a regular table?

If so you could write a script to pull out the drop commands BUT I wouldn't want to run drops automatically, I would have run it manually and verify the outputs to see what tables I am actually dropping.

SELECT 'DROP TABLE '+object_schema_name(o.object_id)+'.'+name+';', name
FROM sys.objects
WHERE name like 'SOME PREFIX%'
OR name LIKE '%SOME SUFFIX%'
AND Type = 'U'

Something like this to display a list of DROP TABLE commands on screen, verify that it's not going to drop anything it shouldn't, then copy paste that column output into a new query window and press F5.

REMEMBER TO MAKE SURE YOU CAN RESTORE IN CASE IT SCREWS UP

Ant-Green SSC Guru Points: 113536 More actions · Answer 2

Do all your interim tables have some sort of static prefix or suffix that differentiates them from a regular table?

If so you could write a script to pull out the drop commands BUT I wouldn't want to run drops automatically, I would have run it manually and verify the outputs to see what tables I am actually dropping.

SELECT 'DROP TABLE '+object_schema_name(o.object_id)+'.'+name+';', name
FROM sys.objects
WHERE name like 'SOME PREFIX%'
OR name LIKE '%SOME SUFFIX%'
AND Type = 'U'

Something like this to display a list of DROP TABLE commands on screen, verify that it's not going to drop anything it shouldn't, then copy paste that column output into a new query window and press F5.

REMEMBER TO MAKE SURE YOU CAN RESTORE IN CASE IT SCREWS UP

rmurray 17656 Valued Member Points: 70 More actions · Answer 3

Yeah - they all start : [dbo].[InterimTable_..........

pietlinden SSC Guru Points: 63572 More actions · Answer 4

Sorry for not minding my own business, but what if you created a schema for the temporary objects? Then when you're sure you're done with them, just use DROP SCHEMA.

...

DROP SCHEMA [IF EXISTS] schema_name;