在表中的SQL Server中查找非空列

小编典典

在表中的SQL Server中查找非空列

sql

我已经阅读了许多答案，但是它们全都适用于PL / SQL或Oracle，对于Microsoft SQL-Server我找不到任何答案。

我的桌子：

CREATE TABLE StudentScore
(
  Student_ID INT PRIMARY KEY,
  Student_Name NVARCHAR (50),
  Student_Score INT
)

GO

INSERT INTO StudentScore VALUES (1,'Ali', NULL)
INSERT INTO StudentScore VALUES (2,'Zaid', 770)
INSERT INTO StudentScore VALUES (3,'Mohd', 1140)
INSERT INTO StudentScore VALUES (4,NULL, 770)
INSERT INTO StudentScore VALUES (5,'John', 1240)
INSERT INTO StudentScore VALUES (6,'Mike', 1140)
INSERT INTO StudentScore VALUES (7,'Goerge', NULL)

如何查找所有非空列的名称。
返回表仅包含非空列

根据评论进行编辑：

我知道的IS_NULLABLE属性Information_schema。但是，仅仅因为一列允许空值并不意味着它实际上将具有空值。如何找出实际上具有空值的列。

我正在寻找与num_nullsMicrosoft SQL-SERVER相当的产品。

阅读 195

2021-03-10

共1个答案

小编典典

您可以通过发出以下命令来实现：

SELECT 
  FORMATMESSAGE('SELECT col = ''%s.%s.%s'' FROM %s.%s HAVING COUNT(*) != COUNT(%s)', 
     QUOTENAME(TABLE_SCHEMA),
     QUOTENAME(TABLE_NAME),
     QUOTENAME(COLUMN_NAME),
     QUOTENAME(TABLE_SCHEMA),
     QUOTENAME(TABLE_NAME),
     QUOTENAME(COLUMN_NAME)
  )
FROM INFORMATION_SCHEMA.COLUMNS
WHERE IS_NULLABLE = 'YES';

db <>
fiddle演示

它将生成用于检查单个列的脚本。

HAVING COUNT(*) != COUNT(col_name) -- it means that column contains at least single NULL

HAVING COUNT(col_name) = 0 AND COUNT(*) != 0 -- it means all values in columns are NULL

可以通过使用STRING_AGG每个表获取单个查询来完善此方法，而使用动态SQL可以避免复制查询的需要。

编辑：

完全烘烤的解决方案：

DECLARE @sql NVARCHAR(MAX);

SELECT @sql = STRING_AGG(
  FORMATMESSAGE('SELECT table_schema = ''%s''
                        ,table_name = ''%s''
                        ,table_col_name = ''%s'' 
                        ,row_num = COUNT(*)
                        ,row_num_non_nulls = COUNT(%s)
                        ,row_num_nulls = COUNT(*) - COUNT(%s)
                 FROM %s.%s', 
     QUOTENAME(TABLE_SCHEMA),
     QUOTENAME(TABLE_NAME),
     QUOTENAME(COLUMN_NAME),
     QUOTENAME(COLUMN_NAME),
     QUOTENAME(COLUMN_NAME),
     QUOTENAME(TABLE_SCHEMA),
     QUOTENAME(TABLE_NAME),
     QUOTENAME(COLUMN_NAME)), ' UNION ALL' + CHAR(13)
               ) WITHIN GROUP(ORDER BY TABLE_SCHEMA, TABLE_NAME)

FROM INFORMATION_SCHEMA.COLUMNS
WHERE IS_NULLABLE = 'YES'
  AND TABLE_NAME = ?        -- filter by table name
  AND TABLE_SCHEMA = ?;     -- filter by schema name

SELECT @sql;
EXEC(@sql);

db <>
fiddle演示

输出：

+---------------+-----------------+------------------+----------+--------------------+---------------+
| table_schema  |   table_name    | table_col_name   | row_num  | row_num_non_nulls  | row_num_nulls |
+---------------+-----------------+------------------+----------+--------------------+---------------+
| [dbo]         | [StudentScore]  | [Student_Name]   |       7  |                 6  |             1 |
| [dbo]         | [StudentScore]  | [Student_Score]  |       7  |                 5  |             2 |
+---------------+-----------------+------------------+----------+--------------------+---------------+

2021-03-10