用SQL生成SQL

    遇到需要批量删除数据中的表的问题，二十来个表，手写DROP TABLE 太麻烦了，虽然现在有很多前端工具都有类似功能，但也需要点N次鼠标后才能完成，要是有外键更麻烦。可以用Python等脚本完，但我想最简单的还是用SQL生 成DROP TABLE语句，在此分享一下。
    用SQL生所DROP TABLE 语句的思路是，只要能从SELECT语句中得到表名即可用以下得到需要的删除脚本
    select concat('DROP TABLE ', tablename) from ........
   
    在MySQL中的 information_schema.tables中得到所有的表名, 所在通过加上数据库条件，就能得到要操作的数据库中的所有表
    show tables也可以得到当前数据库的所有表，但不能(或许是我不知道 ) 用于select中，如果你知道，麻烦告诉我一声 。

    以下语句生成删除test库中的所有表的脚本并保存在c:/drop.sql中
    

mysql>SELECT  
                      CONCAT('drop table ', table_name,';')  
                      INTO OUTFILE 'c:/drop.sql' 
            FROM 
                     information_schema.tables s 
            WHERE 
                     s.TABLE_SCHEMA='test';

 

drop.sql中的内容类似：

 

    drop table b;
    drop table c;
    drop table m;

 

    
    然后执行删除脚本：
    
           mysql>source  c:/drop.sql

     以上例子中的表之间没有主外键约束可以直接删除，如果有主外键约束，可以先删除把外键约束删除，Mysql中约束信息在
    information_schema.TABLE_CONSTRAINTS 表中,也可以生成删除外键的语句
    
     “ alter table
          tablename
       drop
          foreign key foreign-key-name; ”

比如JBPM的表有很多外键约束，可以用以下SQL生成删除JBPM数据库中的所有外键

SELECT
    CONCAT('alter table ',fk.TABLE_NAME, ' drop foreign key ', fk.CONSTRAINT_NAME,';')
FROM
    information_schema.TABLE_CONSTRAINTS fk
WHERE
    fk.CONSTRAINT_SCHEMA='JBPM' AND fk.CONSTRAINT_TYPE='foreign key';

 

执行以上SQL可以生成删除外键的外键的脚本drop_fk.sql

再生成删除表的脚本：

SELECT
    CONCAT('drop table ',t.TABLE_NAME,';')
    INTO OUTFILE 'c:/drop.sql'
FROM
    information_schema.TABLES t
WHERE t.table_schema='jbpm'; 

 

执行删除：

SQL> source c:/drop_fk.slq
SQL> source c:/drop.sql

 

Oracle中可以

    

select 'DROP TABLE' || table_name || ';' from user_tables;

生成删除表的脚本

 

oracle不熟悉，才开始学习，以后补上。

评论

1 楼 longforfreedom 2010-03-04  

自己回复一个，怎么多了一串“<span style="font-size: small;">”编辑的时候又看不到，相删除删除不了。