How to remove all views from mysql database using mysql query / command?

Question

I need to delete all views from my mysql database. How can I do this with a query?

Can someone help me?

Answer 1

Quote from the MySQL Reference Guide :

DROP VIEW [IF EXISTS] view_name [, view_name] ... [RESTRICT | CASCADE] 

DROP VIEW deletes one or more views. You must have a DROP privilege for each view. If any of the views named in the argument list does not exist, MySQL returns an error indicating by name that nonexistent representations it could not delete, but also leaves all views in the existing list.

The IF EXISTS clause prevents errors from occurring for non-existing representations. When this proposal is given, a NOTE is created for each nonexistent view. See Section 12.7.5.41, “SHOW WARNINGS Syntax”.

RESTRICT and CASCADE , if specified, are parsed and ignored.

Answer 2

I used this one:

 /* DROP ALL VIEWS */ SET @views = NULL; SELECT GROUP_CONCAT(table_schema, '.', table_name) INTO @views FROM information_schema.views WHERE table_schema = @database_name; -- Your DB name here SET @views = IFNULL(CONCAT('DROP VIEW ', @views), 'SELECT "No Views"'); PREPARE stmt FROM @views; EXECUTE stmt; DEALLOCATE PREPARE stmt; 

Answer 3

try this unverified code

 DECLARE VIEW_NAME VARCHAR(31); DECLARE VIEW_NAMES CURSOR FOR SELECT table_name FROM information_schema.views; WHERE table_schema = 'DB_Name' OPEN VIEW_NAMES; REPEAT FETCH VIEW_NAMES INTO VIEW_NAME; DROP VIEW VIEW_NAME UNTIL done END REPEAT; CLOSE VIEW_NAMES; END; 

Answer 4

This uses the Ruby method, which will do what you want:

 # @param [Array] databases, eg ['db1', 'db2'] def drop_all_views(databases) views = ActiveRecord::Base.connection.execute("SELECT CONCAT(TABLE_SCHEMA,'.',TABLE_NAME) FROM information_schema.TABLES WHERE TABLE_TYPE = 'VIEW' AND TABLE_SCHEMA IN('#{databases.join("', '")}');") views = views.to_a.flatten # Then drop all of those views from their respective databases views.each do |v| ActiveRecord::Base.connection.execute("DROP VIEW IF EXISTS #{v};") end end