MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Cleanup a MySQL Schema

with 6 comments

My students required way to remove all their tables, views, and constraints from a MySQL database (or the alias schema). Since they’re using referential or foreign key constraints, I also wrote one procedure to drop all foreign key constraints from a database. There’s also another to drop views. The final stored procedure calls the procedure that drops foreign keys, then calls the procedure to drop views before dropping all the tables.

Here’s the dropForeignKeys stored procedure, but if you want to capture ALTER statements that add these back later please check my follow-up Capture MySQL Foreign Keys post.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
-- Provide a log file debugging statement.
SELECT 'DROP PROCEDURE IF EXISTS dropForeignKeys';
 
-- Conditionally drop the procedure.
DROP PROCEDURE IF EXISTS dropForeignKeys;
 
-- Provide a log file debugging statement.
SELECT 'CREATE PROCEDURE dropForeignKeys';
 
-- Change delimiter to create procedure.
DELIMITER $$
 
-- Create procedure.
CREATE PROCEDURE dropForeignKeys
( pv_database          VARCHAR(64)
, pv_referenced_table  VARCHAR(64))
BEGIN
 
  /* Declare local statement variables. */
  DECLARE lv_stmt VARCHAR(1024);
 
  /* Declare local cursor variables. */
  DECLARE lv_table_name       VARCHAR(64);
  DECLARE lv_constraint_name  VARCHAR(64);
 
  /* Declare control variable for handler. */
  DECLARE fetched       INT DEFAULT 0;
 
  /* Declare local cursor. */
  DECLARE foreign_key_cursor CURSOR FOR
    SELECT   rc.table_name
    ,        rc.constraint_name
    FROM     information_schema.referential_constraints rc
    WHERE    constraint_schema = IFNULL(pv_database,database())
    AND      referenced_table_name = pv_referenced_table
    ORDER BY rc.table_name
    ,        rc.constraint_name;
 
  /* Declare a not found record handler to close a cursor loop. */
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1;
 
  /* Open a local cursor. */  
  OPEN foreign_key_cursor;
  cursor_foreign_key: LOOP
 
    FETCH foreign_key_cursor
    INTO  lv_table_name
    ,     lv_constraint_name;
 
    /* Place the catch handler for no more rows found
       immediately after the fetch operation.          */
    IF fetched = 1 THEN LEAVE cursor_foreign_key; END IF;
 
    /* Set a SQL statement by using concatenation. */
    SET @SQL := CONCAT('ALTER TABLE',' ',lv_table_name,' ','DROP FOREIGN KEY',' ',lv_constraint_name);
 
    /* Prepare, run, and deallocate statement. */
    PREPARE lv_stmt FROM @SQL;
    EXECUTE lv_stmt;
    DEALLOCATE PREPARE lv_stmt;
 
  END LOOP cursor_foreign_key;
  CLOSE foreign_key_cursor;  
 
END;
$$
 
-- Reset delimiter to run SQL statements.
DELIMITER ;

Here’s the dropViews stored procedure:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
-- Provide a log file debugging statement.
SELECT 'DROP PROCEDURE IF EXISTS dropViews';
 
-- Conditionally drop the procedure.
DROP PROCEDURE IF EXISTS dropViews;
 
-- Provide a log file debugging statement.
SELECT 'CREATE PROCEDURE dropViews';
 
-- Change delimiter to create procedure.
DELIMITER $$
 
-- Create procedure.
CREATE PROCEDURE dropViews
( pv_database  VARCHAR(64))
BEGIN
 
  /* Declare local statement variables. */
  DECLARE lv_stmt VARCHAR(1024);
 
  /* Declare local cursor variables. */
  DECLARE lv_view_name       VARCHAR(64);
 
  /* Declare control variable for handler. */
  DECLARE fetched       INT DEFAULT 0;
 
  /* Declare local cursor. */
  DECLARE view_cursor CURSOR FOR
    SELECT   v.table_name
    FROM     information_schema.views v
    WHERE    table_schema = IFNULL(pv_database, database())
    ORDER BY v.table_name;
 
  /* Declare a not found record handler to close a cursor loop. */
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1;
 
  /* Open a local cursor. */  
  OPEN view_cursor;
  cursor_view: LOOP
 
    FETCH view_cursor
    INTO  lv_view_name;
 
    /* Place the catch handler for no more rows found
       immediately after the fetch operation.          */
    IF fetched = 1 THEN LEAVE cursor_view; END IF;
 
    /* Set a SQL statement by using concatenation. */
    SET @SQL := CONCAT('DROP VIEW',' ',lv_view_name);
 
    /* Prepare, run, and deallocate statement. */
    PREPARE lv_stmt FROM @SQL;
    EXECUTE lv_stmt;
    DEALLOCATE PREPARE lv_stmt;
 
  END LOOP cursor_view;
  CLOSE view_cursor;  
 
END;
$$
 
-- Reset delimiter to run SQL statements.
DELIMITER ;

Here’s the dropTables stored procedure:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
CREATE PROCEDURE dropTables
( pv_database  VARCHAR(64))
BEGIN
 
  /* Declare local statement variables. */
  DECLARE lv_stmt  VARCHAR(1024);
 
  /* Declare local cursor variables. */
  DECLARE lv_table_name  VARCHAR(64);
 
  /* Declare control variable for handler. */
  DECLARE fetched       INT DEFAULT 0;
 
  /* Declare local cursor. */
  DECLARE table_cursor CURSOR FOR
    SELECT   t.table_name
    FROM     information_schema.tables t
    WHERE    table_schema = IFNULL(pv_database, database())
    ORDER BY t.table_name;
 
  /* Declare a not found record handler to close a cursor loop. */
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1;
 
  /* Drop the views. */
  CALL dropViews(null);
 
  /* Open a local cursor. */  
  OPEN table_cursor;
  cursor_table: LOOP
 
    FETCH table_cursor
    INTO  lv_table_name;
 
    /* Place the catch handler for no more rows found
       immediately after the fetch operation.          */
    IF fetched = 1 THEN LEAVE cursor_table; END IF;
 
    /* Drop the tables. */
    CALL dropForeignKeys(null,lv_table_name);
 
    /* Set a SQL statement by using concatenation. */
    SET @SQL := CONCAT('DROP TABLE',' ',lv_table_name);
 
    /* Prepare, run, and deallocate statement. */
    PREPARE lv_stmt FROM @SQL;
    EXECUTE lv_stmt;
    DEALLOCATE PREPARE lv_stmt;
 
  END LOOP cursor_table;
  CLOSE table_cursor;  
 
END;
$$
 
-- Reset delimiter to run SQL statements.
DELIMITER ;

You put these in a rerunnable script, run it, and then call the dropTables stored procedure. You can pass a database (or schema) name or a null value. When you pass a null value, it uses the current database, like:

CALL dropTables(null);

As always, I hope this helps others.

Written by maclochlainn

February 7th, 2014 at 1:38 am

6 Responses to 'Cleanup a MySQL Schema'

Subscribe to comments with RSS or TrackBack to 'Cleanup a MySQL Schema'.

  1. Extremely useful procedures.
    Is it possible to write a procedure that will recreate the dropped foreign keys?

    Shantanu Oak

    6 Mar 14 at 6:31 am

  2. […] a way to capture foreign keys before removing them […]

  3. Shantanu, I posted a procedure and function here that let you capture foreign keys before removing them, however, you may benefit from exploring the reverse engineering option provided by the MySQL Workbench utility. Hope this helps.

    maclochlainn

    17 Mar 14 at 11:41 pm

  4. […] a way to capture foreign keys […]

    InsideMySQL

    18 Mar 14 at 3:54 am

  5. First, these functions are awesome, and have saved me a bunch of time.

    There is one problem, though. If the table, view or key is a reserved word, the script doesn’t surround them in “, so and error will be thrown.

    Also, it might be nice in the foreign key dropper if you could have it drop all foreign keys in a database if no referenced table is specified. I hacked that bit out for my purposes.

    Thanks for writing these!

    Dan.

    Dan Rogers

    16 May 14 at 5:49 pm

  6. Dan, As to dropping all foreign keys in a database, just change line 35 to this:

    35
    
        AND      referenced_table_name = IFNULL(pv_referenced_table,referenced_table_name)

    maclochlainn

    18 May 14 at 6:53 pm

Leave a Reply