topic Query to show column names in common between multiple tables in Data Engineering

Query to show column names in common between multiple tables

Datanoob123 — Mon, 10 Mar 2025 17:54:25 GMT

Hi all,

I have a large amount of tables that I would like a query to pull the column names present in these tables that are common between all the tables.

I know about show columns, but can't seem to use this or another method to achieve this. This will save me from having to manually check large amounts of tables to see what columns are in common.

Re: Query to show column names in common between multiple tables

Stefan-Koch — Mon, 10 Mar 2025 19:31:57 GMT

hi @Datanoob123

You find all the column names in the system tables of Unity Catalog in the following table:

system.information_schema.columns

Re: Query to show column names in common between multiple tables

KaranamS — Mon, 10 Mar 2025 19:34:05 GMT

Hi @Datanoob123 ,

You can use information_schema in system tables to get the details about columns and tables in Databricks. Here is a sample code that gives you the column names and number of tables with same column name.

SELECT column_name, COUNT(DISTINCT table_name) AS table_count FROM information_schema.columns GROUP BY column_name

Hope this helps!

Re: Query to show column names in common between multiple tables

Datanoob123 — Tue, 11 Mar 2025 14:29:41 GMT

Hi,

I think maybe that view is not available, I get this error: AnalysisException: Table or view not found: information_schema.columns; line 2 pos 5;

Re: Query to show column names in common between multiple tables

Datanoob123 — Tue, 11 Mar 2025 14:30:40 GMT

I get this error, Error in SQL statement: AnalysisException: Catalog namespace is not supported.

Re: Query to show column names in common between multiple tables

Stefan-Koch — Tue, 11 Mar 2025 14:40:30 GMT

could be, system tables are not enabled, or you don't have access to it. Are you a member of the admin group? or can you ask your administrator?

Re: Query to show column names in common between multiple tables

KaranamS — Tue, 11 Mar 2025 21:43:03 GMT

Hi @Datanoob123 ,

I agree with @Stefan-Koch! It could be that you don't have access to the system tables. Please reach out to your Databricks Admin to grant you required permissions to system tables. You can then use the query I shared to get the required results

Cheers!