Menu
I need to delete all views from my MySQL database. How can I do that using query?
Can anyone can help me please?
682
To delete a view, use the DROP VIEW command. DROP VIEW takes one argument: the name of the view to be dropped. A database name can be prepended to the view name. You can add the IF EXISTS syntax.
There are two ways to delete all the data in a MySQL database table. TRUNCATE TABLE tablename; This will delete all data in the table very quickly. In MySQL the table is actually dropped and recreated, hence the speed of the query.
DROP VIEW removes one or more views. You must have the DROP privilege for each view. If any of the views named in the argument list do not exist, MySQL returns an error indicating by name which non-existing views it was unable to drop, but it also drops all of the views in the list that do exist.
To get a list of MySQL views, we can use the SELECT command with LIKE operator. Let us see the syntax first. mysql> SELECT TABLE_SCHEMA, TABLE_NAME -> FROM information_schema.
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;
Quoting from MySQL Reference Manual:
DROP VIEW [IF EXISTS]
view_name [, view_name] ...
[RESTRICT | CASCADE]
DROP VIEW removes one or more views. You must have the DROP privilege for each view. If any of the views named in the argument list do not exist, MySQL returns an error indicating by name which non-existing views it was unable to drop, but it also drops all of the views in the list that do exist.
The IF EXISTS clause prevents an error from occurring for views that don't exist. When this clause is given, a NOTE is generated for each nonexistent view. See Section 12.7.5.41, “SHOW WARNINGS Syntax”.
RESTRICT and CASCADE, if given, are parsed and ignored.
27
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
