SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)

    Drop Tables with Wildcard

    Hi

    A quick question, does anyone know if there is a way to drop tables with a specific name + wildcard;

    I would like to delete multiple tables that begin with '__temp_' like '__temp_atyztyj' and '__temp_pqrtynr' Is there any drop table command that I can drop both these tables without knowing what comes after '__temp_'?

    Regards,
    Steve
    ictus==""

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    query information_schema.tables to get the table names, then use the results to issue your drops
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    You can use the catalog to generate the SQL DROP commands

    Code:
    SELECT 'DROP ' || [...]
    and run the script after that.

    EDIT: a split second too late...

  4. #4
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    Thanks Gents
    ictus==""

  5. #5
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    So I got
    Code:
    SELECT 
       CONCAT(
         "DROP TABLE ",  
          GROUP_CONCAT(TABLE_NAME)
        ) AS stmt 
        FROM information_schema.TABLES 
        WHERE TABLE_SCHEMA = "my_db" AND TABLE_NAME LIKE "__temp_%";
    It generates a stmt like
    Code:
    DROP TABLE __temp_afsrpsy,__temp_dfsposb,__temp_exyrygx,__temp_gfyunsc,__temp_jhwshaj,__temp_khlmgnv,__temp_mhbhtfy,__temp_ondgdqr,__temp_oqucumc,__temp_prmgzax,__temp_qqhlwgi,__temp_vhrmdpu,__temp_xpqhrew,__temp_xraqrss,__temp_yvnplnu
    This is OK as it is a two step process, one to generate the Drop statement and the other to copy and run this statement into the command line and run it; however is there a way to do this in one step in the command line or work_bench without using PHP?

    Yes I'm curious and lazy

    Regards,
    Steve
    ictus==""

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ServerStorm View Post
    This is OK as it is a two step process...
    be aware that only mysql allows you to specify a list of tables in a single DROP statement

    Quote Originally Posted by ServerStorm View Post
    ... is there a way to do this in one step in the command line or work_bench without using PHP?
    i really doubt it, but i'm forever surprised by what some people can come up with
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    Quote Originally Posted by r937 View Post
    be aware that only mysql allows you to specify a list of tables in a single DROP statement
    Ok, I am testing a php object that in the end will do self cleanup of the temp table in question. I am not quite far enough along with it to drop the table as part of the routine as I still need to study the contents of the imported temp table so I will not be using this technique in production. I've used standard SQL for all SQL I use in the application and I don't plan on implementing any dialect features unless I'm really need it. Thank you for point this out
    Quote Originally Posted by r937 View Post
    i really doubt it, but i'm forever surprised by what some people can come up with
    Ok, I will go with what I have an not be lazy

    Regards,
    Steve
    ictus==""


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •