How to drop all tables from database using one SQL query without destroying database. It's impossible in Postgresql, MySQL ,Oracle. But we can generate sql.
#MySQL mysql> SELECT CONCAT('DROP TABLE ', TABLE_NAME,' ;') FROM information_schema.TABLES WHERE TABLE_SCHEMA='mysql'; +----------------------------------------+ | CONCAT('DROP TABLE ', TABLE_NAME,' ;') | +----------------------------------------+ | DROP TABLE columns_priv ; | | DROP TABLE db ; | | DROP TABLE func ; | | DROP TABLE help_category ; | | DROP TABLE help_keyword ; | | DROP TABLE help_relation ; | | DROP TABLE help_topic ; | | DROP TABLE host ; | | DROP TABLE proc ; | | DROP TABLE procs_priv ; | | DROP TABLE tables_priv ; | | DROP TABLE time_zone ; | | DROP TABLE time_zone_leap_second ; | | DROP TABLE time_zone_name ; | | DROP TABLE time_zone_transition ; | | DROP TABLE time_zone_transition_type ; | | DROP TABLE user ; | +----------------------------------------+ 17 rows in set (0.02 sec) Execute drop commands many times becouse of foreign keys ;-) #Postgresql SELECT 'DROP TABLE '||c.relname ||' CASCADE;' FROM pg_catalog.pg_class c JOIN pg_catalog.pg_roles r ON r.oid = c.relowner LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 1; ?column? ------------------------------------------------- DROP TABLE auth_group_permissions CASCADE; DROP TABLE auth_group CASCADE; DROP TABLE auth_message CASCADE; DROP TABLE auth_permission CASCADE; DROP TABLE auth_user_groups CASCADE; ... #Oracle SELECT 'DROP TABLE '||table_name||';' FROM user_tables; 'DROPTABLE'||TABLE_NAME||';' ------------------------------------------ DROP TABLE SYS_EXPORT_SCHEMA_01; DROP TABLE MON_NO_CHECK; DROP TABLE REWRITE_TABLE;
No comments:
Post a Comment