---
title: "Retrieve Table Columns with Specific Keywords in PySpark SQL"
description: "The content outlines a process using PySpark SQL to identify columns with the keyword \"id\" in three specified tables: EMP1, DEPT1, and ALLOWANCE1. A Python script retrieves and filters these columns, creating a DataFrame that displays the tables along with their respective matching columns for easy viewing within a database schema."
url: https://srinimf.com/2024/12/31/retrieve-table-columns-with-specific-keywords-in-pyspark-sql/
date: 2024-12-31
modified: 2026-10-24
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2024/12/create-a-highly-detailed-high-resolution-image-featuring-a-computer-screen-1.png?fit=1024%2C768&ssl=1
categories: ["databricks", "PySpark"]
tags: ["Pyspark SQL examples"]
type: post
lang: en
---

# Retrieve Table Columns with Specific Keywords in PySpark SQL

[PySpark SQL](https://srinimf.com/2024/12/22/calculate-match-percentage-in-pyspark-sql/) can find table columns that contain specific keywords. I have three tables: EMP1, DEPT1, and ALLOWANCE1, each with columns that include the word “id.” My goal is to retrieve each table along with its column names.

Related[SQL AVG Column Function: How to Use it](https://srinimf.com/2015/08/20/db2-avg-column-function-best-example/)

## Databricks: PySpark SQL Create Table EMP1

```
CREATE TABLE EMP1 (
        EMPNAME STRING,
        EMP_ID INT,
        SALARY FLOAT,
        PF_ID STRING
    )
```

Related[PySpark Databricks Optimizations Vs. Clustered Index: Top Differences](https://srinimf.com/2024/09/20/pyspark-databricks-optimizations-vs-clustered-index-top-differences/)

![Creating table emp1](https://i0.wp.com/srinimf.com/wp-content/uploads/2024/12/image-9.png?resize=709%2C344&ssl=1)

## Databricks: PySpark SQL Create Table DEPT1

```
CREATE TABLE DEPT1 (        DEPT_ID INT,        DEPT_NAME STRING    )
```

![Creating table dept1](https://i0.wp.com/srinimf.com/wp-content/uploads/2024/12/image-10.png?resize=710%2C256&ssl=1)

## Databricks: PySpark SQL Create Table ALLOWANCE1

```
CREATE TABLE ALLOWANCE1 (        EMP_ID INT,        ALLOWANCE FLOAT    )
```

![Creating table allowance1](https://i0.wp.com/srinimf.com/wp-content/uploads/2024/12/image-11.png?resize=706%2C258&ssl=1)

## Schema That Shows All the Tables

The “default” schema has all three tables. You can see it here.

![Schema details](https://i0.wp.com/srinimf.com/wp-content/uploads/2024/12/image-12.png?resize=786%2C392&ssl=1)

## Python logic that retrieves all the tables and columns

```
%python schema_name = "default"search_Word = ["id"]final_list = []# Get all tables in the schemalist_tables = spark.sql(f"SHOW TABLES IN {schema_name}")for table in list_tables.collect():    table_name = table.tableName  # Extract the table name    if "bkp" in table_name.lower() or "backup" in table_name.lower():        continue  # Skip backup tables    else:        # Get column details for the table        columns = spark.sql(f"DESCRIBE {schema_name}.{table_name}").collect()        # Use list comprehension to filter matching columns        matching_columns = [            col.col_name for col in columns             if any(word in col.col_name.lower() for word in search_Word)        ]        # Append table name and matching columns to the final list        if matching_columns:            final_list.append((table_name, matching_columns))# Create a DataFrame from the final listdf = spark.createDataFrame(final_list, ["Table_name", "Column_names"])# Display the DataFramedisplay(df)
```

This code finds tables and columns in a schema that contain the search term “id” and returns a list of these tables and their matching columns in a DataFrame format.

### Step-by-Step Explanation:

1. **Schema and Search Word Definition**:`schema_name = "default" search_Word = ["id"] final_list = []`
   - `schema_name = "default"`: Specifies the schema where you are looking for tables (e.g., `default` schema in Spark).
   - `search_Word = ["id"]`: A list of words you want to search for in column names. In this case, it will search for any column name containing “id”.
   - `final_list = []`: Initializes an empty list to store the results.
2. **Fetch Tables in the Schema**: `list_tables = spark.sql(f"SHOW TABLES IN {schema_name}")`
   - This Spark SQL command retrieves all the [tables in](https://srinimf.com/2023/07/24/how-to-retrieve-two-tables-data-using-db2-join/) the specified schema (in this case, `"default"`).
3. **Iterate Through Each Table**: `for table in list_tables.collect(): table_name = table.tableName # Extract the table name`
   - The `for` loop iterates through the list of tables in the schema. The `table.tableName` extracts the name of each table.
4. **Skip Backup Tables**: `if "bkp" in table_name.lower() or "backup" in table_name.lower(): continue # Skip backup tables`
   - If the table name contains the “bkp” or “backup” (case insensitive), it skips that table using the `continue` statement.
5. **Get Column Details for the Table**: `columns = spark.sql(f"DESCRIBE {schema_name}.{table_name}").collect()`
   - The `DESCRIBE` command gets metadata for each table, including column names, types, and details.
   - `.collect()` converts the result into a list of `Row` objects, which is easier to [work with](https://srinimf.com/2024/06/09/easy-ways-to-work-with-xml-files-python/) in Python.
6. **Filter Columns Based on `search_Word`**: `matching_columns = [ col.col_name for col in columns if any(word in col.col_name.lower() for word in search_Word) ]`
   - This list comprehension filters the column names. It checks if any word in `search_Word` is [present in](https://srinimf.com/2016/06/27/how-many-sysdummy-tables-are-present-in-db2/) the column name (`col.col_name`).
   - The `lower()` the function ensures case-insensitive matching, so “ID”, “id”, or “Id” will all match.
7. **Store Results in `final_list`**: `if matching_columns: final_list.append((table_name, matching_columns))`
   - If there are any matching columns (i.e., if `matching_columns` is not empty), it appends a tuple of `(table_name, matching_columns)` to `final_list`.
8. **Create DataFrame**: `df = spark.createDataFrame(final_list, ["Table_name", "Column_names"])`
   - After processing all tables, a DataFrame is created from `final_list`. The DataFrame will have two columns: `Table_name` (the name of the table) and `Column_names` (a list of matching column names).
9. **Display DataFrame**:`display(df)`
   - This will display the DataFrame in Databricks as a table, showing the `Table_name` and `Column_names`.

Output

![Final output](https://i0.wp.com/srinimf.com/wp-content/uploads/2024/12/image-13.png?resize=682%2C380&ssl=1)

```
Table_name	Column_namesallowance1	["EMP_ID"]dept1	        ["DEPT_ID"]emp1	        ["EMP_ID","PF_ID"]
```

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2024%2F12%2F31%2Fretrieve-table-columns-with-specific-keywords-in-pyspark-sql%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2024%2F12%2Fcreate-a-highly-detailed-high-resolution-image-featuring-a-computer-screen-1.png%3Ffit%3D1024%252C768%26ssl%3D1&description=Retrieve%20Table%20Columns%20with%20Specific%20Keywords%20in%20PySpark%20SQL)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2024/12/31/retrieve-table-columns-with-specific-keywords-in-pyspark-sql/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2024/12/31/retrieve-table-columns-with-specific-keywords-in-pyspark-sql/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2024/12/31/retrieve-table-columns-with-specific-keywords-in-pyspark-sql/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2024/12/31/retrieve-table-columns-with-specific-keywords-in-pyspark-sql/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20Retrieve%20Table%20Columns%20with%20Specific%20Keywords%20in%20PySpark%20SQL&body=https%3A%2F%2Fsrinimf.com%2F2024%2F12%2F31%2Fretrieve-table-columns-with-specific-keywords-in-pyspark-sql%2F&share=email)
-

### Like this:

Like Loading…