如何在查询表列总数的SQL中新增可存储NULL值的列统计？

Got it, let's adjust your SQL query to include the count of columns that allow NULL values. Here's the modified version:

SELECT 
  Table_Schema, 
  Table_Name, 
  COUNT(*) AS total_columns,
  SUM(CASE WHEN IS_NULLABLE = 'YES' THEN 1 ELSE 0 END) AS null_allowed_columns
FROM Information_Schema.Columns 
GROUP BY Table_Schema, Table_Name 
HAVING Table_Schema = 'schema';

Breakdown of the changes: #

• COUNT(*) AS total_columns: This retains your original logic of counting all columns per table, with a clearer alias to make the result set more readable.
• SUM(CASE WHEN IS_NULLABLE = 'YES' THEN 1 ELSE 0 END) AS null_allowed_columns: This is the key addition. The Information_Schema.Columns view includes an IS_NULLABLE field that equals YES if the column accepts NULL values. We use a CASE statement to assign a value of 1 to these columns and 0 to others, then sum those values to get the total count of nullable columns for each table.
• Your existing GROUP BY and HAVING clauses stay untouched—they still group results by schema and table, and filter to only return data from your target schema.

内容的提问来源于stack exchange，提问作者N Rika