Friday, October 16, 2009

How to drop all tables from database using one SQL query



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