Query to show column names in common between multiple tables

[Datanoob123]

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.

[Stefan-Koch]

hi @Datanoob123 

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

system.information_schema.columns

[Datanoob123]

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

[KaranamS]

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!

[Datanoob123]

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;

[Stefan-Koch]

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?

[KaranamS]

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!