How to find tables that contain a specific column in SQL using Python?

Last Updated : 26 Nov, 2020

MySQL server is an open-source relational database management system that is a major support for web-based applications. Databases and related tables are the main component of many websites and applications as the data is stored and exchanged over the web. In order to access MySQL databases from a web server, we use various modules in Python such as PyMySQL, mysql.connector, etc.

In this article, we are going to get the tables having a specific column irrespective of the database. First, we are going to connect to a server. The SQL query that is going to be used is:

 select tab.table_schema as database_name,tab.table_name 
 from information_schema.tables as tab 
 inner join information_schema.columns as col
 on col.table_schema = tab.table_schema 
 and col.table_name = tab.table_name 
 and column_name = 'Specified_Column_Name' 
 where tab.table_type = 'BASE TABLE' 
 order by tab.table_schema, tab.table_name;

Below are some programs which depict how to get the MySQL tables having a particular column:

Example 1

Below is the table description of all the tables, the sample database having the following tables which are going to be accessed by a Python script:

Below is the program to get the details of MySQL tables having a particular column:

Python3

# Module Imports 
import mysql.connector as mariadb 
import sys 
  
  
# Connect to MariaDB Platform 
try: 
    conn = mariadb.connect( 
        user="root", 
        password="", 
        database="gfg"
    ) 
  
except mariadb.Error as e: 
    print(f"Error connecting to MariaDB Platform: {e}") 
    sys.exit(1) 
  
  
# Get cursor object 
cur = conn.cursor() 
  
  
# Get the table and database details having a particular column 
cur.execute("select tab.table_schema as database_name,tab.table_name \ 
             from information_schema.tables as tab \ 
             inner join information_schema.columns as col \ 
             on col.table_schema = tab.table_schema \ 
             and col.table_name = tab.table_name \ 
             and column_name = 'Worker_ID' \ 
             where tab.table_type = 'BASE TABLE' \ 
             order by tab.table_schema, tab.table_name;") 
  
  
# Display the tables 
for(database_name, tab) in cur: 
    print(f"Table Name: {tab}Database Name: {database_name}") 

Output:

Example 2

Here is another example to get table details irrespective of the database having a particular column, below is the description of all the tables:

Below is the python script to get table details having particular column Names:

Python3

# Module Imports 
import mysql.connector as mariadb 
import sys 
  
  
# Connect to MariaDB Platform 
try: 
    conn = mariadb.connect( 
        user="root", 
        password="", 
        database="gfg"
    ) 
  
except mariadb.Error as e: 
    print(f"Error connecting to MariaDB Platform: {e}") 
    sys.exit(1) 
  
  
# Get cursor object 
cur = conn.cursor() 
  
  
# Get the table and database details having a particular column 
cur.execute("select tab.table_schema as database_name,tab.table_name \ 
             from information_schema.tables as tab \ 
             inner join information_schema.columns as col \ 
             on col.table_schema = tab.table_schema \ 
             and col.table_name = tab.table_name \ 
             and column_name = 'Names' \ 
             where tab.table_type = 'BASE TABLE' \ 
             order by tab.table_schema, tab.table_name;") 
  
  
# Display the tables 
for(database_name, tab) in cur: 
    print(f"\n\nTable Name: {tab}\nDatabase Name: {database_name}") 