Home How to retrieve the count of columns for a table in SQL and combine it with another SQL query?

Questions

How to retrieve the count of columns for a table in SQL and combine it with another SQL query?

October 22, 2023

I need the count of columns of a table, in below SQL I am getting the desired output, but I also need the count of columns of that ‘PAYMENT’ table.

SELECT COUNT(*) AS TotalRowCount,     
MAX(MODIFIEDUTC) AS newMODIFIEDUTC, 
MIN(MODIFIEDUTC) AS oldMODIFIEDUTC, 
MAX(CREATEDUTC) AS newCREATEDUTC, 
MIN(CREATEDUTC) AS oldCREATEDUTC, 
DATEDIFF(DAY, MAX(MODIFIEDUTC), GETDATE()) AS Freshness 
FROM NAVI.PAYMENT;

How do I get columns count without disturbing above sql query. I need to run this query in SNOWFLAKE and also in Microsoft SQL server management studio.

>Solution :

A cross-platform query would look like

select count(*)
from INFORMATION_SCHEMA.COLUMNS 
where table_schema = 'NAVI' and table_name = 'PAYMENT';

To incorporate in existing query you would simply do:

SELECT COUNT(*) AS TotalRowCount,     
..., (
  select count(*)
  from INFORMATION_SCHEMA.COLUMNS 
  where table_schema = 'NAVI' and table_name = 'PAYMENT'
) as ColumnCount
FROM NAVI.PAYMENT;