MYSQL FAQs

= MySQL Quick Reference =

Truncate multiple tables in a MySQL database in one command
SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES where  table_schema in ('db1_name','db2_name'); FOREIGN KEY CHECK SET FOREIGN_KEY_CHECKS = 1;

SELECT

Concat('TRUNCATE TABLE ', TABLE_NAME)

FROM

information_schema.tables

WHERE

table_schema = 'db_name';

Find all foreign keys
In case of one wants to find all PK-FK relation to a particular table then can use the below query. Example there is department table with primary key as id. We want to know all the tables that have a foreign key to this department table - PK id.

SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = 'database' AND REFERENCED_TABLE_NAME = 'table' AND REFERENCED_COLUMN_NAME = 'column'; Source Reference is here.