May 8, 2025 at 9:20 am
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.
May 8, 2025 at 10:19 am
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
May 8, 2025 at 10:20 am
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
May 8, 2025 at 10:27 am
Yeah - they all start : [dbo].[InterimTable_..........
May 9, 2025 at 2:03 am
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;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply