You can group them all in the same alter statement: alter table tbl alter col1 drop not null, alter col2 drop not null, … You can also retrieve the list of relevant columns from the catalog, if you feel like writing a do block to generate the needed sql. For instance, something like: select a.attname … Read more
You cannot do it with just a single MySQL command, however you can use MySQL to construct the statement for you: In the MySQL shell or through PHPMyAdmin, use the following query SELECT CONCAT( ‘DROP TABLE ‘, GROUP_CONCAT(table_name) , ‘;’ ) AS statement FROM information_schema.tables WHERE table_name LIKE ‘myprefix_%’; This will generate a DROP statement … Read more
Simply you can use the following SQL Script to delete the index in MySQL: alter table fuinfo drop index email;
This might be useful to someone ending up here from a search. Make sure you’re trying to drop a table and not a view. SET foreign_key_checks = 0; — Drop tables drop table … — Drop views drop view … SET foreign_key_checks = 1; SET foreign_key_checks = 0 is to set foreign key checks to … Read more
BEGIN FOR cur_rec IN (SELECT object_name, object_type FROM user_objects WHERE object_type IN (‘TABLE’, ‘VIEW’, ‘MATERIALIZED VIEW’, ‘PACKAGE’, ‘PROCEDURE’, ‘FUNCTION’, ‘SEQUENCE’, ‘SYNONYM’, ‘PACKAGE BODY’ )) LOOP BEGIN IF cur_rec.object_type=”TABLE” THEN EXECUTE IMMEDIATE ‘DROP ‘ || cur_rec.object_type || ‘ “‘ || cur_rec.object_name || ‘” CASCADE CONSTRAINTS’; ELSE EXECUTE IMMEDIATE ‘DROP ‘ || cur_rec.object_type || ‘ “‘ || … Read more
You can generate statement like this: DROP TABLE t1, t2, t3, … and then use prepared statements to execute it: SET FOREIGN_KEY_CHECKS = 0; SET @tables = NULL; SELECT GROUP_CONCAT(‘`’, table_schema, ‘`.`’, table_name, ‘`’) INTO @tables FROM information_schema.tables WHERE table_schema=”database_name”; — specify DB name here. SET @tables = CONCAT(‘DROP TABLE ‘, @tables); PREPARE stmt FROM … Read more
The best and most efficient way is to catch the “table not found” exception: this avoids the overhead of checking if the table exists twice; and doesn’t suffer from the problem that if the DROP fails for some other reason (that might be important) the exception is still raised to the caller: BEGIN EXECUTE IMMEDIATE … Read more
I found the generated set of drop statements useful, and recommend these tweaks: Limit the generated drops to your database like this: SELECT concat(‘DROP TABLE IF EXISTS `’, table_name, ‘`;’) FROM information_schema.tables WHERE table_schema=”MyDatabaseName”; Note 1: This does not execute the DROP statements, it just gives you a list of them. You will need to … Read more