cleanup/dropping of all interimtables

  • 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.

  • 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

  • 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

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

  • 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