topic Re: How to get all the tables name with a specific column or columns in a database? in Data Engineering

How to get all the tables name with a specific column or columns in a database?

ramankr48 — Tue, 18 Oct 2022 11:08:43 GMT

let's say there is a database db in which 700 tables are there, and we need to find all the tables name in which column "project_id" is present.

just an example for ubderstanding the questions.

Re: How to get all the tables name with a specific column or columns in a database?

Anonymous — Tue, 18 Oct 2022 11:53:00 GMT

databaseName = "db"
desiredColumn = "project_id"
database = spark.sql(f"show tables in {databaseName} ").collect()
tablenames = []
for row in database:
  cols = spark.table(row.tableName).columns
  if desiredColumn in cols:
    tablenames.append(row.tableName)

Something close to that should work.

Re: How to get all the tables name with a specific column or columns in a database?

ramankr48 — Tue, 18 Oct 2022 11:58:32 GMT

Thanks josephk it worked

Re: How to get all the tables name with a specific column or columns in a database?

Hubert-Dudek — Tue, 18 Oct 2022 18:09:52 GMT

Re: How to get all the tables name with a specific column or columns in a database?

Being-UK — Mon, 27 Feb 2023 17:43:52 GMT

Thanks @Joseph Kambourakis

but the code seems to rendering errors at my end:

com.immuta.spark.exceptions.NoSuchDataSourceException: A data source with the table name '`v_table_name`' does not exist, is not in the current project, or is not accessible by the current user.

I've changed the original table name to 'v_table_name', as it happenes the table name is the first table on the schema, so it seems the code is searching through the schema but fails on the first table for some reason. Thanks

Re: How to get all the tables name with a specific column or columns in a database?

Anonymous — Tue, 14 Mar 2023 18:04:40 GMT

Sounds like an immuta problem, ask them. Def not a spark error.