Bulk delete specified tables in the specified database

Source: Internet
Author: User

-- ------------------------------------------------------------------------------------------------------------- -1, query: Get bulk deletion of specified tables in the specified database;--2, execute: Copy and re-execute the resulting results;--selectconcat (' drop table ', TABLE_NAME, '; ') Frominformation_schema. Tableswhereinformation_schema. TABLES. table_name like '%_copy '--Gets the table and Table_schema = ' db_emob ' of all surfaces in the database that end in _copy;                        --Query the specified database----------------------------------------------------------------------------------------------------- -------



Query How many tables are in a database in MySQL

SELECT COUNT (*) TABLES, table_schema from INFORMATION_SCHEMA. TABLES
WHERE table_schema = ' database ' GROUP by Table_schema;

This is the first time contact INFORMATION_SCHEMA this database,

The INFORMATION_SCHEMA database is a MySQL-band that provides a way to access database metadata. What is meta data? Metadata is data about the data, such as the database name or table name, the data type of the column, or access permissions. Some of the other terms used to express this information include the data dictionary and the system catalog.
In MySQL, think of Information_schema as a database, specifically an information database. It holds information about all the other databases maintained by the MySQL server. such as database name, database table, table column data type and access permissions. In Information_schema, there are a few read-only tables. They are actually views, not basic tables, so you will not be able to see any files associated with them.

information_schema database table Description:

schemata Table : Provides information for all databases in the current MySQL instance. This table is the result of show databases.

Tables Table : Provides information about the tables in the database, including views. Describes in detail which schema, table type, table engine, creation time, and so on, a table belongs to. This table is the result of show tables from SchemaName.

Columns Table: provides the column information in the table. Describes in detail all the columns of a table and the information for each column. This table is the result of show columns from Schemaname.tablename.

Statistics Table : Provides information about the index of a table. This table is the result of show index from Schemaname.tablename.

user_privileges(User Rights) Table: gives information about the full range of permissions. This information is derived from the Mysql.user authorization form. Non-standard tables.

schema_privileges(program permissions) Table: Provides information about schema (database) permissions. This information is from the Mysql.db authorization form. Non-standard tables.

table_privileges(table permissions) Table: Gives information about table permissions. This information is derived from the Mysql.tables_priv authorization form. Non-standard tables.

column_privileges(column permissions) Table: Gives information about column permissions. This information is derived from the Mysql.columns_priv authorization form. Non-standard tables.

Character_sets(character set) Table: Provides information about the available character sets for MySQL instances. This table is the show CHARACTER set result set.

Collations table: provides cross-reference information about each character set.

collation_character_set_applicability Table : Indicates the character set that can be used for proofreading. These columns are equivalent to the first two display fields of show collation.

table_constraints table: A table describing the existence of constraints. And the constraint type of the table.

key_column_usage Table: describes the key columns that have constraints.

Routines Table: provides information about storing subroutines (stored programs and functions). At this point, the routines table does not contain a custom function (UDF). A column named "Mysql.proc name" indicates the corresponding INFORMATION_SCHEMA. The Mysql.proc table column for the routines table.

Views table: gives information about the view in the database. You need to have show views permission, otherwise you cannot view the view information.

Triggers table: provides information about the triggering program. You must have super privileges to view the table


Bulk delete specified tables in the specified database

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.