mysql - update - use of view in sql




How to delete all views from mysql database using mysql query/command? (3)

Here's a Ruby method that will do what you want:

# @param [Array] databases, e.g. ['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

I need to delete all views from my mysql database. How can I do that using query?

Can anyone can help me please?


I've been using 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;

try this untested 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;




views