How to count the columns from tables in Databricks in SQL

Please, how can I count the number of columns from tables in a SQL notebook in Databricks? The information_schema.columns is not working.

I was trying to run the query below:

select 'data_beat' Table_Name
         ,max(_createTime_) as Last_Create_Date
         ,max(_upt_) as Last_Update_Date 
         ,**(select count(*)
            from information_schema.columns
           where Table_name = 'data_beat'
          ) as Column_count**
		  ,(select count(*)
				from data_lineage.field_dependencies
				where source like 'ett.data_beat%'
		) as Count_to_L1
     from ett.data_beat

Expected Results are:

| Table_Name   | Last_Update_Date | Last_Create_Date | Columns_Count | Count_to_L1 |
| --------     | --------         |
|ett.data_beat | 5/10/2023        | 5/10/2023        | 51            |12           |

I tried in my environment and got same error.

As per this Documentation, and as far as I understood, INFORMATION_SCHEMA requires unity catalogue to be created and it will give the details for those tables under that unity catalogue. This might be reason for it to not identifying INFORMATION_SCHEMA.

So, make sure your tables are under a unity catalogue to use INFORMATION_SCHEMA.

And another workaround can be using some Pyspark code in between the SQL cells to store the column details in a temporary view and using it to get the count.

My Sample:

%python
df=spark.sql('show  columns  from sample1')
df.createOrReplaceTempView("columns_sample1")

%sql
select count(*) as count from columns_sample1;