
- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
How to find all tables that contains two specific columns in MySQL?
To find two specific column names, use information_schema.columns Here, I am using Id in place of columnA and Name in place of columnB −
mysql> select table_name as TableNameFromWebDatabase -> from information_schema.columns -> where column_name IN ('Id', 'Name') -> group by table_name -> having count(*) = 3;
This will produce the following output. Following are the tables with columns Id and Name −
+--------------------------+ | TableNameFromWebDatabase | +--------------------------+ | student | | distinctdemo | | secondtable | | groupconcatenatedemo | | indemo | | ifnulldemo | | demotable211 | | demotable212 | | demotable223 | | demotable233 | | demotable251 | | demotable255 | +--------------------------+ 12 rows in set (0.25 sec)
To prove, let us check the description of one of the tables. Following is the query −
mysql> desc demotable233;
This will produce the following output. Here, you can see we have the Int and Name columns −
+-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | Id | int(11) | NO | PRI | NULL | auto_increment | | Name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec)
- Related Articles
- How to find all tables that contains columnA and columnB in MySQL?
- Rename all tables and columns to lower case in MySQL?
- How to find tables with a specific column name in MySQL?
- How to move data between two tables with columns in different MySQL databases?
- How to find missing value between two MySQL Tables?
- Select all records if it contains specific number in MySQL?
- MySQL - Select all records if it contains specific number?
- Find documents that contains specific field in MongoDB?
- Find a specific column in all the tables in a database?
- Find MongoDB documents that contains specific field?
- How to concatenate all columns in MySQL?
- Concatenate columns from different tables in MySQL
- How can I sum columns across multiple tables in MySQL?
- How to display all the MySQL tables in one line?
- How to remove rows that contains coded missing value for all columns in an R data frame?

Advertisements