ms sql server how to check table has “id” column and count rows if “id” exist

There are too many tables in my SQL Server db. Most of them have an ‘id’ column, but some do not. I want to know which table(s) doesn’t have the ‘id’ column and to count the rows where id=null if an ‘id’ column exists. The query results may look like this:

TABLE_NAME |  HAS_ID |  ID_NULL_COUNT | ID_NOT_NULL_COUNT
  table1   |  false  |     0          |    0
  table2   |  true   |    10          |    100

How do I write this query?

Answer

Building query:

WITH cte AS (
  SELECT t.*, has_id = CASE WHEN COLUMN_NAME = 'ID' THEN 'true' ELSE 'false' END
  FROM INFORMATION_SCHEMA.TABLES t
  OUTER APPLY (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS c
               WHERE t.TABLE_NAME = c.TABLE_NAME
                 AND t.[TABLE_SCHEMA] = c.[TABLE_SCHEMA]
                 AND c.COLUMN_NAME = 'id') s
  WHERE t.TABLE_SCHEMA IN (...)
)
SELECT 
 query_to_run = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
   'SELECT tab_name      = ''<tab_name>'',
           has_id        = ''<has_id>'',
           id_null_count = <id_null_count>,
           id_not_null_count = <id_not_null_count>
    FROM <schema_name>.<tab_name>'
,'<tab_name>', TABLE_NAME)
,'<schema_name>', TABLE_SCHEMA)
,'<has_id>', has_id)
,'<id_null_count>', CASE WHEN has_id = 'false' THEN '0' ELSE 'SUM(CASE WHEN id IS NULL THEN 1 END)' END)
,'<id_not_null_count>', CASE WHEN has_id = 'false' THEN '0' ELSE 'COUNT(id)' END)
FROM cte;

Copy the output and execute in separate window. UNION ALL could be added to get single resultset.

db<>fiddle demo