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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy